runtimetask Correlated subqueries · hard
Last known daily maximum
For the Philadelphia station USW00094732 and calendar dates 2024-11-10 to 2024-11-25, return date, tmax_c (NULL when not reported) and last_known_tmax: the tmax_c of the latest observation on or before that date.
what to return
- Columns
datetmax_clast_known_tmaxin this order- Rows
- 16 rows · any order
- Skill
- Carry the latest known value onto days with no observation. Refresher: CROSS JOIN & a complete date grid →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result16 rows · any order
| date | tmax_c | last_known_tmax |
|---|---|---|
| 2024-11-10 | 15 | 15 |
| 2024-11-11 | 23.3 | 23.3 |
| 2024-11-12 | 14.4 | 14.4 |
first 3 of 16 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.
calendar10 columns · 1,096 rows
| datedate · unique id | yearnumber | monthnumber | day_of_yearnumber | day_of_weeknumber | meteorological_seasontext | day_of_monthnumber | day_nametext | is_weekendnumber | quarternumber |
|---|---|---|---|---|---|---|---|---|---|
| 2022-01-01 | 2022 | 1 | 1 | 6 | winter | 1 | Saturday | 1 | 1 |
| 2022-01-02 | 2022 | 1 | 2 | 0 | winter | 2 | Sunday | 1 | 1 |
| 2022-01-03 | 2022 | 1 | 3 | 1 | winter | 3 | Monday | 0 | 1 |
| 2022-01-04 | 2022 | 1 | 4 | 2 | winter | 4 | Tuesday | 0 | 1 |
| 2022-01-05 | 2022 | 1 | 5 | 3 | winter | 5 | Wednesday | 0 | 1 |
| 2022-01-06 | 2022 | 1 | 6 | 4 | winter | 6 | Thursday | 0 | 1 |
- datedate · unique id
- 2022-01-01
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 1
- day_of_weeknumber
- 6
- meteorological_seasontext
- winter
- day_of_monthnumber
- 1
- day_nametext
- Saturday
- is_weekendnumber
- 1
- quarternumber
- 1
- datedate · unique id
- 2022-01-02
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 2
- day_of_weeknumber
- 0
- meteorological_seasontext
- winter
- day_of_monthnumber
- 2
- day_nametext
- Sunday
- is_weekendnumber
- 1
- quarternumber
- 1
- datedate · unique id
- 2022-01-03
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 3
- day_of_weeknumber
- 1
- meteorological_seasontext
- winter
- day_of_monthnumber
- 3
- day_nametext
- Monday
- is_weekendnumber
- 0
- quarternumber
- 1
- datedate · unique id
- 2022-01-04
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 4
- day_of_weeknumber
- 2
- meteorological_seasontext
- winter
- day_of_monthnumber
- 4
- day_nametext
- Tuesday
- is_weekendnumber
- 0
- quarternumber
- 1
- datedate · unique id
- 2022-01-05
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 5
- day_of_weeknumber
- 3
- meteorological_seasontext
- winter
- day_of_monthnumber
- 5
- day_nametext
- Wednesday
- is_weekendnumber
- 0
- quarternumber
- 1
- datedate · unique id
- 2022-01-06
- yearnumber
- 2022
- monthnumber
- 1
- day_of_yearnumber
- 6
- day_of_weeknumber
- 4
- meteorological_seasontext
- winter
- day_of_monthnumber
- 6
- day_nametext
- Thursday
- is_weekendnumber
- 0
- quarternumber
- 1
first 6 of 1,096 rows · your queries use all of them
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.