views.sql

-- Reusable views. Applied by build_db.py after loading; the numbered
-- analysis queries in this folder build on these.

-- Annual electricity sales per state, total and by sector, in TWh.
CREATE VIEW v_state_sales AS
SELECT
    year,
    state_code,
    SUM(sales_mwh) / 1e6                                              AS total_twh,
    SUM(CASE WHEN sector = 'residential' THEN sales_mwh END) / 1e6    AS residential_twh,
    SUM(CASE WHEN sector = 'commercial'  THEN sales_mwh END) / 1e6    AS commercial_twh,
    SUM(CASE WHEN sector = 'industrial'  THEN sales_mwh END) / 1e6    AS industrial_twh
FROM retail_sales
GROUP BY year, state_code;

-- Per-state linear trend of total demand, fit by ordinary least squares
-- on the 2010-2019 baseline years (before COVID and the data-center
-- buildout), then extended across all years. OLS from first principles:
--   slope = cov(x, y) / var(x),  intercept = mean(y) - slope * mean(x)
CREATE VIEW v_demand_trend AS
WITH baseline AS (
    SELECT
        state_code,
        (AVG(year * total_twh) - AVG(year) * AVG(total_twh))
            / (AVG(year * year) - AVG(year) * AVG(year)) AS slope,
        AVG(year)      AS mean_year,
        AVG(total_twh) AS mean_twh
    FROM v_state_sales
    WHERE year BETWEEN 2010 AND 2019
    GROUP BY state_code
),
fit AS (
    SELECT
        state_code,
        slope,
        mean_twh - slope * mean_year AS intercept
    FROM baseline
)
SELECT
    s.year,
    s.state_code,
    s.total_twh,
    f.intercept + f.slope * s.year               AS trend_twh,
    s.total_twh - (f.intercept + f.slope * s.year) AS excess_twh
FROM v_state_sales s
JOIN fit f USING (state_code);

-- Annual net generation per state in TWh, all producers, all sources.
CREATE VIEW v_state_generation AS
SELECT
    year,
    state_code,
    generation_mwh / 1e6 AS generation_twh
FROM net_generation
WHERE producer_type = 'Total Electric Power Industry'
  AND energy_source = 'Total';