runtimetask Multiple joins · hard
Grid review 4: does demand balance?
For May 2024, compare demand with net_generation − total_interchange per authority-hour. Return ba_code, compared_hours (hours where all three values are known), imbalanced_hours (absolute difference above 2% of demand) and avg_abs_gap_mw (rounded to 0).
what to return
- Columns
ba_codecompared_hoursimbalanced_hoursavg_abs_gap_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
- Reconcile two reports of the same hour. Refresher: Revenue & payment reconciliation →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result12 rows · any order
| ba_code | compared_hours | imbalanced_hours | avg_abs_gap_mw |
|---|---|---|---|
| BPAT | 744 | 744 | 2923 |
| CISO | 672 | 408 | 894 |
| DUK | 744 | 0 | 0 |
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
generation15 columns · 52,405 rows
| ba_codetext · links to balancing_authorities | hour_ending_utcdate & time · unique id | net_generation_mwnumber | total_interchange_mwnumber | adjusted_net_generation_mwnumber | adjusted_total_interchange_mwnumber | coal_mwnumber | natural_gas_mwnumber | nuclear_mwnumber | petroleum_mwnumber | hydro_mwnumber | solar_mwnumber | wind_mwnumber | other_mwnumber | unknown_mwnumber |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BPAT | 2024-01-01 09:00 | 7993 | 4982 | 7993 | 4982 | NULL | 663 | 1154 | NULL | 5955 | 0 | 35 | 188 | NULL |
| BPAT | 2024-01-01 10:00 | 7853 | 4839 | 7853 | 4839 | NULL | 667 | 1155 | NULL | 5799 | -1 | 50 | 184 | NULL |
| BPAT | 2024-01-01 11:00 | 7838 | 4849 | 7838 | 4849 | NULL | 667 | 1154 | NULL | 5778 | 0 | 55 | 185 | NULL |
| BPAT | 2024-01-01 12:00 | 7935 | 4923 | 7935 | 4923 | NULL | 667 | 1154 | NULL | 5897 | 0 | 32 | 184 | NULL |
| BPAT | 2024-01-01 13:00 | 8203 | 5015 | 8203 | 5015 | NULL | 670 | 1155 | NULL | 6176 | -1 | 19 | 184 | NULL |
| BPAT | 2024-01-01 14:00 | 8410 | 5019 | 8410 | 5019 | NULL | 666 | 1154 | NULL | 6374 | 0 | 36 | 180 | NULL |
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 09:00
- net_generation_mwnumber
- 7993
- total_interchange_mwnumber
- 4982
- adjusted_net_generation_mwnumber
- 7993
- adjusted_total_interchange_mwnumber
- 4982
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 663
- nuclear_mwnumber
- 1154
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 5955
- solar_mwnumber
- 0
- wind_mwnumber
- 35
- other_mwnumber
- 188
- unknown_mwnumber
- NULL
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 10:00
- net_generation_mwnumber
- 7853
- total_interchange_mwnumber
- 4839
- adjusted_net_generation_mwnumber
- 7853
- adjusted_total_interchange_mwnumber
- 4839
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 667
- nuclear_mwnumber
- 1155
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 5799
- solar_mwnumber
- -1
- wind_mwnumber
- 50
- other_mwnumber
- 184
- unknown_mwnumber
- NULL
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 11:00
- net_generation_mwnumber
- 7838
- total_interchange_mwnumber
- 4849
- adjusted_net_generation_mwnumber
- 7838
- adjusted_total_interchange_mwnumber
- 4849
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 667
- nuclear_mwnumber
- 1154
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 5778
- solar_mwnumber
- 0
- wind_mwnumber
- 55
- other_mwnumber
- 185
- unknown_mwnumber
- NULL
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 12:00
- net_generation_mwnumber
- 7935
- total_interchange_mwnumber
- 4923
- adjusted_net_generation_mwnumber
- 7935
- adjusted_total_interchange_mwnumber
- 4923
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 667
- nuclear_mwnumber
- 1154
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 5897
- solar_mwnumber
- 0
- wind_mwnumber
- 32
- other_mwnumber
- 184
- unknown_mwnumber
- NULL
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 13:00
- net_generation_mwnumber
- 8203
- total_interchange_mwnumber
- 5015
- adjusted_net_generation_mwnumber
- 8203
- adjusted_total_interchange_mwnumber
- 5015
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 670
- nuclear_mwnumber
- 1155
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 6176
- solar_mwnumber
- -1
- wind_mwnumber
- 19
- other_mwnumber
- 184
- unknown_mwnumber
- NULL
- ba_codetext · links to balancing_authorities
- BPAT
- hour_ending_utcdate & time · unique id
- 2024-01-01 14:00
- net_generation_mwnumber
- 8410
- total_interchange_mwnumber
- 5019
- adjusted_net_generation_mwnumber
- 8410
- adjusted_total_interchange_mwnumber
- 5019
- coal_mwnumber
- NULL
- natural_gas_mwnumber
- 666
- nuclear_mwnumber
- 1154
- petroleum_mwnumber
- NULL
- hydro_mwnumber
- 6374
- solar_mwnumber
- 0
- wind_mwnumber
- 36
- other_mwnumber
- 180
- unknown_mwnumber
- NULL
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.