human
Schema:
station (id, name, lat, long, dock_count, city, installation_date)
status (station_id, bikes_available, docks_available, time)
trip (id, duration, start_date, start_station_name, start_station_id, end_date, end_station_name, end_station_id, bike_id, subscription_type, zip_code)
weather (date, max_temperature_f, mean_temperature_f, min_temperature_f, max_dew_point_f, mean_dew_point_f, min_dew_point_f, max_humidity, mean_humidity, min_humidity, max_sea_level_pressure_inches, mean_sea_level_pressure_inches, min_sea_level_pressure_inches, max_visibility_miles, mean_visibility_miles, min_visibility_miles, max_wind_Speed_mph, mean_wind_speed_mph, max_gust_speed_mph, precipitation_inches, cloud_cover, events, wind_dir_degrees, zip_code)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the id for the trips that lasted at least as long as the average duration of trips in zip code 94103.
assistant
SELECT id FROM trip WHERE duration >= (SELECT avg(duration) FROM trip WHERE zip_code = 94103)
human
Find the ids and names of stations from which at least 200 trips started.
assistant
SELECT start_station_id , start_station_name FROM trip GROUP BY start_station_name HAVING COUNT(*) >= 200
human
List all the distinct stations from which a trip of duration below 100 started.
assistant
SELECT DISTINCT start_station_name FROM trip WHERE duration < 100
human
What are the names of stations that are located in Palo Alto city but have never been the ending point of trips more than 100 times?
assistant
SELECT name FROM station WHERE city = 'Palo Alto' EXCEPT SELECT end_station_name FROM trip GROUP BY end_station_name HAVING count(*) > 100
human
How many trips started from Mountain View city and ended at Palo Alto city?
assistant
SELECT count(*) FROM station AS T1 JOIN trip AS T2 JOIN station AS T3 JOIN trip AS T4 ON T1.id = T2.start_station_id AND T2.id = T4.id AND T3.id = T4.end_station_id WHERE T1.city = 'Mountain View' AND T3.city = 'Palo Alto'
human
Count the number of trips that did not end in San Francisco city.
assistant
SELECT COUNT( * ) FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id = T2.id WHERE T2.city != "San Francisco" ;