runtimetask Conditional aggregation · medium
Three New York stations side by side
For January 2024, return observation_date, central_park, jfk and laguardia: each station’s tmax_c that day (NULL when not reported).
what to return
- Columns
observation_datecentral_parkjfklaguardiain this order- Rows
- 31 rows · any order
- Skill
- Turn one row per station into one column per station. Refresher: Conditional aggregation →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result31 rows · any order
| observation_date | central_park | jfk | laguardia |
|---|---|---|---|
| 2024-01-01 | 8.3 | 8.3 | 8.3 |
| 2024-01-02 | 5.6 | 5.6 | 6.1 |
| 2024-01-03 | 6.1 | 7.2 | 6.1 |
first 3 of 31 rows
data you can query
Weather dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
daily_weather10 columns · 109,214 rows
| station_idtext · links to stations | observation_datedate · links to calendar | tmax_cdecimal | tmin_cdecimal | precipitation_mmdecimal | precip_tracenumber | snowfall_mmdecimal | snow_depth_mmdecimal | avg_wind_speed_msdecimal | peak_gust_msdecimal |
|---|---|---|---|---|---|---|---|---|---|
| USW00003131 | 2022-01-01 | 17.2 | 7.2 | 0 | 0 | NULL | NULL | 2 | 6.3 |
| USW00003131 | 2022-01-02 | 20 | 5 | 0 | 0 | NULL | NULL | 1.1 | 5.4 |
| USW00003131 | 2022-01-03 | 17.8 | 3.9 | 0 | 0 | NULL | NULL | 1.1 | 4.5 |
| USW00003131 | 2022-01-04 | 17.2 | 4.4 | 0 | 0 | NULL | NULL | 1.4 | 4.5 |
| USW00003131 | 2022-01-05 | 21.1 | 5.6 | 0 | 0 | NULL | NULL | 0.9 | 4 |
| USW00003131 | 2022-01-06 | 22.2 | 7.2 | 0 | 0 | NULL | NULL | 0.6 | 3.6 |
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-01
- tmax_cdecimal
- 17.2
- tmin_cdecimal
- 7.2
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 2
- peak_gust_msdecimal
- 6.3
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-02
- tmax_cdecimal
- 20
- tmin_cdecimal
- 5
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 1.1
- peak_gust_msdecimal
- 5.4
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-03
- tmax_cdecimal
- 17.8
- tmin_cdecimal
- 3.9
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 1.1
- peak_gust_msdecimal
- 4.5
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-04
- tmax_cdecimal
- 17.2
- tmin_cdecimal
- 4.4
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 1.4
- peak_gust_msdecimal
- 4.5
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-05
- tmax_cdecimal
- 21.1
- tmin_cdecimal
- 5.6
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 0.9
- peak_gust_msdecimal
- 4
- station_idtext · links to stations
- USW00003131
- observation_datedate · links to calendar
- 2022-01-06
- tmax_cdecimal
- 22.2
- tmin_cdecimal
- 7.2
- precipitation_mmdecimal
- 0
- precip_tracenumber
- 0
- snowfall_mmdecimal
- NULL
- snow_depth_mmdecimal
- NULL
- avg_wind_speed_msdecimal
- 0.6
- peak_gust_msdecimal
- 3.6
first 6 of 109,214 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.