runtimetask Medians, shares & ratios · medium
Observation coverage by station
For 2023, return station_id, reported_days, tmax_days (days with a known tmax_c) and coverage_pct: reported_days ÷ 365 × 100, rounded to 1. Include stations with no 2023 rows as 0.
what to return
- Columns
station_idreported_daystmax_dayscoverage_pctin this order- Rows
- 100 rows · any order
- Skill
- Measure completeness against a calendar. Refresher: CROSS JOIN & a complete date grid →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result100 rows · any order
| station_id | reported_days | tmax_days | coverage_pct |
|---|---|---|---|
| USW00003131 | 365 | 365 | 100 |
| USW00003184 | 365 | 363 | 100 |
| USW00003822 | 365 | 365 | 100 |
first 3 of 100 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.
stations10 columns · 100 rows
| station_idtext · unique id | station_nametext | city_idnumber · links to cities | is_primarynumber | latitudedecimal | longitudedecimal | elevation_mdecimal | first_observationdate | last_observationdate | reported_daysnumber |
|---|---|---|---|---|---|---|---|---|---|
| USW00003131 | San Diego Montgomery Fld | 10 | 1 | 32.8144 | -117.1375 | 127.4 | 2022-01-01 | 2024-12-31 | 1096 |
| USW00003184 | Phoenix Deer Valley Muni Airport | 5 | 1 | 33.6903 | -112.0661 | 453.2 | 2022-01-01 | 2024-12-31 | 1096 |
| USW00003822 | Savannah International Airport | 24 | 1 | 32.1314 | -81.2022 | 8.8 | 2022-01-01 | 2024-12-31 | 1096 |
| USW00003888 | Atlanta Fulton Co Airport | 23 | 1 | 33.7775 | -84.5247 | 245.1 | 2022-01-01 | 2024-12-31 | 1095 |
| USW00003928 | Wichita | 32 | 1 | 37.6544 | -97.4431 | 426.7 | 2022-01-01 | 2024-12-31 | 1096 |
| USW00003960 | Cahokia St Louis Dwtn Airport | 45 | 1 | 38.5639 | -90.1486 | 123.4 | 2022-01-01 | 2024-12-31 | 1096 |
- station_idtext · unique id
- USW00003131
- station_nametext
- San Diego Montgomery Fld
- city_idnumber · links to cities
- 10
- is_primarynumber
- 1
- latitudedecimal
- 32.8144
- longitudedecimal
- -117.1375
- elevation_mdecimal
- 127.4
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1096
- station_idtext · unique id
- USW00003184
- station_nametext
- Phoenix Deer Valley Muni Airport
- city_idnumber · links to cities
- 5
- is_primarynumber
- 1
- latitudedecimal
- 33.6903
- longitudedecimal
- -112.0661
- elevation_mdecimal
- 453.2
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1096
- station_idtext · unique id
- USW00003822
- station_nametext
- Savannah International Airport
- city_idnumber · links to cities
- 24
- is_primarynumber
- 1
- latitudedecimal
- 32.1314
- longitudedecimal
- -81.2022
- elevation_mdecimal
- 8.8
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1096
- station_idtext · unique id
- USW00003888
- station_nametext
- Atlanta Fulton Co Airport
- city_idnumber · links to cities
- 23
- is_primarynumber
- 1
- latitudedecimal
- 33.7775
- longitudedecimal
- -84.5247
- elevation_mdecimal
- 245.1
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1095
- station_idtext · unique id
- USW00003928
- station_nametext
- Wichita
- city_idnumber · links to cities
- 32
- is_primarynumber
- 1
- latitudedecimal
- 37.6544
- longitudedecimal
- -97.4431
- elevation_mdecimal
- 426.7
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1096
- station_idtext · unique id
- USW00003960
- station_nametext
- Cahokia St Louis Dwtn Airport
- city_idnumber · links to cities
- 45
- is_primarynumber
- 1
- latitudedecimal
- 38.5639
- longitudedecimal
- -90.1486
- elevation_mdecimal
- 123.4
- first_observationdate
- 2022-01-01
- last_observationdate
- 2024-12-31
- reported_daysnumber
- 1096
first 6 of 100 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.