As discussed in the initial post, The Auction Units Report is a National Energy Market (NEM) report published by AEMO on NEM-web here: https://nemweb.com.au/Reports/CURRENT/Auction_Units_Reports/
It provides directional energy‐auction outcomes for each interconnector and interconnector direction over a trading week, capturing power volumes (mw) as well as financial surpluses and payments.
To build some queries in Grafana I need to explore the table and SQL outputs.
I want to see how power was purchased from which interconnector and region.
SELECT start_date, interconnector_id, from_region_id, purchased_units
FROM auction_units
ORDER BY start_date desc
;Output…
start_date | interconnector_id | from_region_id | purchased_units
---------------------+-------------------+----------------+-----------------
2025-07-13 00:00:00 | NSW1-QLD1 | NSW1 | 550
2025-07-13 00:00:00 | NSW1-QLD1 | QLD1 | 1242
2025-07-13 00:00:00 | VIC1-NSW1 | NSW1 | 1330
2025-07-13 00:00:00 | VIC1-NSW1 | VIC1 | 1506
2025-07-13 00:00:00 | V-SA | SA1 | 805
2025-07-13 00:00:00 | V-SA | VIC1 | 920
2025-07-06 00:00:00 | NSW1-QLD1 | NSW1 | 550
2025-07-06 00:00:00 | NSW1-QLD1 | QLD1 | 1242
2025-07-06 00:00:00 | VIC1-NSW1 | NSW1 | 1330
2025-07-06 00:00:00 | VIC1-NSW1 | VIC1 | 1506
2025-07-06 00:00:00 | V-SA | VIC1 | 920
2025-07-06 00:00:00 | V-SA | SA1 | 805
...
...For every week we have the interconnector and the direction flow giving us 6 possibilities. This output might be hard to pivot. Testing a concatenation (SQL flow field) of “connector_id” and “from_region_id” to effectively reduce a field to pivot without losing information.
SELECT
start_date,
interconnector_id || ' from region ' || from_region_id AS flow,
purchased_units
FROM auction_units
ORDER BY
start_date DESC,
interconnector_id ASC
;Output…
start_date | flow | purchased_units
---------------------+----------------------------+-----------------
2025-07-13 00:00:00 | NSW1-QLD1 from region NSW1 | 550
2025-07-13 00:00:00 | NSW1-QLD1 from region QLD1 | 1242
2025-07-13 00:00:00 | VIC1-NSW1 from region NSW1 | 1330
2025-07-13 00:00:00 | VIC1-NSW1 from region VIC1 | 1506
2025-07-13 00:00:00 | V-SA from region SA1 | 805
2025-07-13 00:00:00 | V-SA from region VIC1 | 920
2025-07-06 00:00:00 | NSW1-QLD1 from region NSW1 | 550
2025-07-06 00:00:00 | NSW1-QLD1 from region QLD1 | 1242
2025-07-06 00:00:00 | VIC1-NSW1 from region NSW1 | 1330
2025-07-06 00:00:00 | VIC1-NSW1 from region VIC1 | 1506
2025-07-06 00:00:00 | V-SA from region VIC1 | 920
2025-07-06 00:00:00 | V-SA from region SA1 | 805
...
...To pivot the data into columns (so each interconnector gets its own column, while each row remains as a time series) requires a lot more SQL.
WITH flows AS (
SELECT
start_date AS week,
interconnector_id
|| ' from region '
|| from_region_id AS flow,
purchased_units
FROM auction_units
)
SELECT
week,
MAX(
CASE WHEN flow = 'NSW1-QLD1 from region NSW1' THEN purchased_units END
) AS "NSW1-QLD1 from NSW1",
MAX(
CASE WHEN flow = 'NSW1-QLD1 from region QLD1' THEN purchased_units END
) AS "NSW1-QLD1 from QLD1",
MAX(
CASE WHEN flow = 'VIC1-NSW1 from region NSW1' THEN purchased_units END
) AS "VIC1-NSW1 from NSW1",
MAX(
CASE WHEN flow = 'VIC1-NSW1 from region VIC1' THEN purchased_units END
) AS "VIC1-NSW1 from VIC1",
MAX(
CASE WHEN flow = 'V-SA from region SA1' THEN purchased_units END
) AS "V-SA from SA1",
MAX(
CASE WHEN flow = 'V-SA from region VIC1' THEN purchased_units END
) AS "V-SA from VIC1"
FROM flows
GROUP BY week
ORDER BY week DESC;Output…
week | NSW1-QLD1 from NSW1 | NSW1-QLD1 from QLD1 | VIC1-NSW1 from NSW1 | VIC1-NSW1 from VIC1 | V-SA from SA1 | V-SA from VIC1
---------------------+---------------------+---------------------+---------------------+---------------------+---------------+----------------
2025-07-13 00:00:00 | 550 | 1242 | 1330 | 1506 | 805 | 920
2025-07-06 00:00:00 | 550 | 1242 | 1330 | 1506 | 805 | 920
2025-06-29 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-06-22 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-06-15 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-06-08 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-06-01 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-05-25 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-05-18 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-05-11 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
2025-05-04 00:00:00 | 560 | 1236 | 1337 | 1535 | 799 | 895
...
...Experimenting in Grafana I’ve produced the below visualisations:
- Bar Guage showing the interconnectors, direction and the volume (mw).