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 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
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
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
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
For each zip code, find the ids of all trips that have a higher 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 ;