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
For each zip code, return the average mean temperature of August there.
assistant
SELECT zip_code , avg(mean_temperature_f) FROM weather WHERE date LIKE '8/%' GROUP BY zip_code
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
When and in what zip code did max temperature reach 80?
assistant
SELECT date , zip_code FROM weather WHERE max_temperature_f >= 80
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
Give me the zip code where the average mean humidity is below 70 and at least 100 trips took place.
assistant
SELECT zip_code FROM weather GROUP BY zip_code HAVING avg(mean_humidity) < 70 INTERSECT SELECT zip_code FROM trip GROUP BY zip_code HAVING count(*) >= 100
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 ;