runtimetask LEFT JOIN · medium
Grid review 2: missing demand hours
For every balancing authority with hourly detail, return ba_code, hours (rows in Q1 2024), missing_demand (hours with NULL demand_mw) and coverage_pct (known hours ÷ hours × 100, 2 decimals).
what to return
- Columns
ba_codehoursmissing_demandcoverage_pctin this order- Rows
- 12 rows · any order
- Watch for
- Every stage starts from the original tables.
- Use hour_ending_utc for ordering and periods; MW over one hour equals MWh.
- NULL measurements are unknown: exclude them from averages and ratios, never treat them as zero.
- Skill
- Quantify data gaps per authority. Refresher: Ratios, ROUND & NULLIF →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result12 rows · any order
| ba_code | hours | missing_demand | coverage_pct |
|---|---|---|---|
| BPAT | 2176 | 0 | 100 |
| CISO | 2176 | 0 | 100 |
| DUK | 2179 | 0 | 100 |
first 3 of 12 rows
data you can query
Energy 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.
balancing_authorities6 columns · 62 rows
| ba_codetext · unique id | ba_nametext | time_zonetext | region_codetext · links to regions | generation_onlynumber | hourly_detailnumber |
|---|---|---|---|---|---|
| AECI | Associated Electric Cooperative, Inc. | Central | MIDW | 0 | 0 |
| AVA | Avista Corporation | Pacific | NW | 0 | 0 |
| AVRN | Avangrid Renewables, LLC | Pacific | NW | 1 | 0 |
| AZPS | Arizona Public Service Company | Arizona | SW | 0 | 0 |
| BANC | Balancing Authority of Northern California | Pacific | CAL | 0 | 0 |
| BCHA | British Columbia Hydro and Power Authority | CAN | 0 | 0 |
- ba_codetext · unique id
- AECI
- ba_nametext
- Associated Electric Cooperative, Inc.
- time_zonetext
- Central
- region_codetext · links to regions
- MIDW
- generation_onlynumber
- 0
- hourly_detailnumber
- 0
- ba_codetext · unique id
- AVA
- ba_nametext
- Avista Corporation
- time_zonetext
- Pacific
- region_codetext · links to regions
- NW
- generation_onlynumber
- 0
- hourly_detailnumber
- 0
- ba_codetext · unique id
- AVRN
- ba_nametext
- Avangrid Renewables, LLC
- time_zonetext
- Pacific
- region_codetext · links to regions
- NW
- generation_onlynumber
- 1
- hourly_detailnumber
- 0
- ba_codetext · unique id
- AZPS
- ba_nametext
- Arizona Public Service Company
- time_zonetext
- Arizona
- region_codetext · links to regions
- SW
- generation_onlynumber
- 0
- hourly_detailnumber
- 0
- ba_codetext · unique id
- BANC
- ba_nametext
- Balancing Authority of Northern California
- time_zonetext
- Pacific
- region_codetext · links to regions
- CAL
- generation_onlynumber
- 0
- hourly_detailnumber
- 0
- ba_codetext · unique id
- BCHA
- ba_nametext
- British Columbia Hydro and Power Authority
- time_zonetext
- region_codetext · links to regions
- CAN
- generation_onlynumber
- 0
- hourly_detailnumber
- 0
first 6 of 62 rows · your queries use all of them
hourly_demand9 columns · 52,405 rows
| ba_codetext · links to balancing_authorities | hour_ending_utcdate & time · unique id | hour_ending_localdate & time | demand_mwnumber | forecast_mwnumber | local_datedate · links to calendar | hour_numbernumber | adjusted_demand_mwnumber | demand_imputednumber |
|---|---|---|---|---|---|---|---|---|
| BPAT | 2024-01-01 09:00 | 2024-01-01 01:00 | 6302 | 6309 | 2024-01-01 | 1 | 6302 | 0 |
| BPAT | 2024-01-01 10:00 | 2024-01-01 02:00 | 6230 | 6236 | 2024-01-01 | 2 | 6230 | 0 |
| BPAT | 2024-01-01 11:00 | 2024-01-01 03:00 | 6179 | 6265 | 2024-01-01 | 3 | 6179 | 0 |
| BPAT | 2024-01-01 12:00 | 2024-01-01 04:00 | 6197 | 6277 | 2024-01-01 | 4 | 6197 | 0 |
| BPAT | 2024-01-01 13:00 | 2024-01-01 05:00 | 6378 | 6394 | 2024-01-01 | 5 | 6378 | 0 |
| BPAT | 2024-01-01 14:00 | 2024-01-01 06:00 | 6607 | 6625 | 2024-01-01 | 6 | 6607 | 0 |
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 09:00
- hour_ending_localdate & time
- 2024-01-01 01:00
- demand_mwnumber
- 6302
- forecast_mwnumber
- 6309
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 1
- adjusted_demand_mwnumber
- 6302
- demand_imputednumber
- 0
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 10:00
- hour_ending_localdate & time
- 2024-01-01 02:00
- demand_mwnumber
- 6230
- forecast_mwnumber
- 6236
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 2
- adjusted_demand_mwnumber
- 6230
- demand_imputednumber
- 0
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 11:00
- hour_ending_localdate & time
- 2024-01-01 03:00
- demand_mwnumber
- 6179
- forecast_mwnumber
- 6265
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 3
- adjusted_demand_mwnumber
- 6179
- demand_imputednumber
- 0
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 12:00
- hour_ending_localdate & time
- 2024-01-01 04:00
- demand_mwnumber
- 6197
- forecast_mwnumber
- 6277
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 4
- adjusted_demand_mwnumber
- 6197
- demand_imputednumber
- 0
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 13:00
- hour_ending_localdate & time
- 2024-01-01 05:00
- demand_mwnumber
- 6378
- forecast_mwnumber
- 6394
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 5
- adjusted_demand_mwnumber
- 6378
- demand_imputednumber
- 0
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 14:00
- hour_ending_localdate & time
- 2024-01-01 06:00
- demand_mwnumber
- 6607
- forecast_mwnumber
- 6625
- local_datedate · links to calendar
- 2024-01-01
- hour_numbernumber
- 6
- adjusted_demand_mwnumber
- 6607
- demand_imputednumber
- 0
first 6 of 52,405 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.