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
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
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 id of the trip that has the shortest duration?
assistant
SELECT id FROM trip ORDER BY duration LIMIT 1
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 are the ids and durations of the trips with the top 3 durations?
assistant
SELECT id , duration FROM trip ORDER BY duration DESC LIMIT 3 ;