human
Schema:
train (id, train_number, name, origin, destination, time, interval)
station (id, network_name, services, local_authority)
route (train_id, station_id)
weekly_weather (station_id, day_of_week, high_temperature, low_temperature, precipitation, wind_speed_mph)
Please answer with SQL.
assistant
Sure, I understand.
human
Hi, can you please tell me what is the ID of the station whose maximum precipitation is higher than 50?
assistant
SELECT t2.id FROM weekly_weather AS t1 JOIN station AS t2 ON t1.station_id = t2.id GROUP BY t1.station_id HAVING MAX( t1.precipitation ) > 50;
human
thank you, could you please also let me know their names and local authorities?
assistant
SELECT t2.id , t2.network_name , t2.local_authority FROM weekly_weather AS t1 JOIN station AS t2 ON t1.station_id = t2.id GROUP BY t1.station_id HAVING MAX( t1.precipitation ) > 50 ;