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
What is the average longitude of stations that never had bike availability more than 10?
assistant
SELECT avg(long) FROM station WHERE id NOT IN (SELECT station_id FROM status GROUP BY station_id HAVING max(bikes_available) > 10)
human
List all the cities in a decreasing order of each city's stations' highest latitude.
assistant
SELECT city FROM station GROUP BY city ORDER BY max(lat) DESC
human
For each city, return the highest latitude among its stations.
assistant
SELECT city , max(lat) FROM station GROUP BY city
human
What are the ids of stations that have latitude above 37.4 and never had bike availability below 7?
assistant
SELECT id FROM station WHERE lat > 37.4 EXCEPT SELECT station_id FROM status GROUP BY station_id HAVING min(bikes_available) < 7
human
What are the name, latitude, and city of the station with the lowest latitude?
assistant
SELECT name , lat , city FROM station ORDER BY lat LIMIT 1
human
What are the names of all stations with a latitude smaller than 37.5?
assistant
SELECT name FROM station WHERE lat < 37.5 ;