runtimetask CTEs · hard
Grid review 3: June peak hours and ties
For June 2024, return ba_code, hour_ending_utc and demand_mw for each authority’s highest known demand hour, keeping every tied hour.
what to return
- Columns
ba_codehour_ending_utcdemand_mwin 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
- Find each authority’s peak, including tied hours. Refresher: Top N per group & ties →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result12 rows · any order
| ba_code | hour_ending_utc | demand_mw |
|---|---|---|
| BPAT | 2024-06-26 02:00 | 7848 |
| CISO | 2024-06-25 03:00 | 38678 |
| DUK | 2024-06-26 21:00 | 21303 |
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.
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.