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
Give me ids for all the trip that took place in a zip code area with average mean temperature above 60.
assistant
SELECT T1.id FROM trip AS T1 JOIN weather AS T2 ON T1.zip_code = T2.zip_code GROUP BY T2.zip_code HAVING avg(T2.mean_temperature_f) > 60
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
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
What is the total and maximum duration of trips with bike id 636?
assistant
SELECT sum(duration) , max(duration) FROM trip WHERE bike_id = 636
human
What is the id of the trip that has the shortest duration?
assistant
SELECT id FROM trip ORDER BY duration LIMIT 1
human
What are the ids of the trips that lasted the longest and how long did they last?
assistant
SELECT id , duration FROM trip ORDER BY duration DESC LIMIT 3 ;