runtimetask UNION / INTERSECT / EXCEPT · medium
Two measures, one stream
For CISO on hours ending 2024-06-20 13:00 through 18:00, return hour_ending_utc, metric ('demand' or 'net_generation') and mw in one stream, ordered by hour then metric.
what to return
- Columns
hour_ending_utcmetricmwin this order- Rows
- 12 rows · in the order asked
- Skill
- Combine measures from two tables into one long result. Refresher: Combine sets with UNION →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result12 rows · in this order
| hour_ending_utc | metric | mw |
|---|---|---|
| 2024-06-20 13:00 | demand | 22167 |
| 2024-06-20 13:00 | net_generation | 16503 |
| 2024-06-20 14:00 | demand | 22848 |
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.