7a1bbca339
- Replace $0 electricity prices with real EIA retail-sales data (IND sector) with demand-based hourly variation (0.8x-1.2x) - Add sparkline component and alerts feed to dashboard home - Add animated number transitions to hero metric cards - Fix ticker tape price direction (green/red arrows with % change) - Fix AI milestone annotation alignment on price charts - Fix SQL cartesian products in getDemandByRegion and getRegionPriceHeatmap using CTEs for independent aggregation - Add unique composite constraints to prevent duplicate data - Add bearer token auth to ingestion API routes - Add 30s fetch timeouts to EIA and FRED API clients - Add regionCode validation in server actions - Fix docker-compose: localhost-only port binding, correct volume path - Fix seed script to preserve ingested time-series data
35 lines
1006 B
SQL
35 lines
1006 B
SQL
-- @param {DateTime} $1:startDate
|
|
-- @param {DateTime} $2:endDate
|
|
-- @param {String} $3:regionCode - pass 'ALL' to return all regions
|
|
WITH demand_agg AS (
|
|
SELECT
|
|
ep.region_id,
|
|
date_trunc('day', ep.timestamp) AS day,
|
|
AVG(ep.demand_mw) AS avg_demand,
|
|
MAX(ep.demand_mw) AS peak_demand
|
|
FROM electricity_prices ep
|
|
WHERE ep.timestamp BETWEEN $1 AND $2
|
|
GROUP BY ep.region_id, date_trunc('day', ep.timestamp)
|
|
),
|
|
dc_agg AS (
|
|
SELECT
|
|
d.region_id,
|
|
COUNT(*)::INT AS datacenter_count,
|
|
COALESCE(SUM(d.capacity_mw), 0) AS total_dc_capacity_mw
|
|
FROM datacenters d
|
|
GROUP BY d.region_id
|
|
)
|
|
SELECT
|
|
r.code AS region_code,
|
|
r.name AS region_name,
|
|
da.day,
|
|
da.avg_demand,
|
|
da.peak_demand,
|
|
COALESCE(dc.datacenter_count, 0)::INT AS datacenter_count,
|
|
COALESCE(dc.total_dc_capacity_mw, 0) AS total_dc_capacity_mw
|
|
FROM grid_regions r
|
|
INNER JOIN demand_agg da ON da.region_id = r.id
|
|
LEFT JOIN dc_agg dc ON dc.region_id = r.id
|
|
WHERE ($3 = 'ALL' OR r.code = $3)
|
|
ORDER BY r.code, da.day
|