06_planned_capacity.sql

-- Does the proposed build-out cover the demand that has already shown up?
-- Convert every proposed generator's nameplate MW into an ESTIMATED
-- annual TWh using assumed fleet-average capacity factors, then compare
-- with the state's 2024 excess demand. Batteries carry a factor of zero:
-- they shift energy within the day but add none across a year.
-- These are estimates: actual output depends on when projects come
-- online (the pipeline spans roughly 2025-2030) and how hard they run.

WITH proposed_est AS (
    SELECT
        g.state_code,
        g.technology,
        SUM(g.nameplate_mw) / 1e3 AS nameplate_gw,
        SUM(g.nameplate_mw * cf.assumed_cf * 8766) / 1e6 AS est_annual_twh
    FROM generators g
    JOIN capacity_factors cf ON cf.technology = g.technology
    WHERE g.status_group = 'proposed'
    GROUP BY g.state_code, g.technology
),
by_state AS (
    SELECT
        state_code,
        ROUND(SUM(nameplate_gw), 2)     AS proposed_gw,
        ROUND(SUM(est_annual_twh), 2)   AS est_new_twh,
        ROUND(SUM(CASE WHEN technology = 'Solar Photovoltaic'
                       THEN est_annual_twh ELSE 0 END), 2) AS est_solar_twh,
        ROUND(SUM(CASE WHEN technology LIKE 'Natural Gas%'
                       THEN est_annual_twh ELSE 0 END), 2) AS est_gas_twh,
        ROUND(SUM(CASE WHEN technology LIKE '%Wind%'
                       THEN est_annual_twh ELSE 0 END), 2) AS est_wind_twh,
        ROUND(SUM(CASE WHEN technology = 'Batteries'
                       THEN nameplate_gw ELSE 0 END), 2)   AS battery_gw
    FROM proposed_est
    GROUP BY state_code
)
SELECT
    b.state_code,
    st.state_name,
    ROUND(t.excess_twh, 1) AS excess_demand_2024_twh,
    b.proposed_gw,
    b.est_new_twh,
    b.est_solar_twh,
    b.est_gas_twh,
    b.est_wind_twh,
    b.battery_gw,
    CASE WHEN t.excess_twh > 1
         THEN ROUND(b.est_new_twh / t.excess_twh, 2)
    END AS pipeline_coverage_ratio
FROM by_state b
JOIN states st ON st.state_code = b.state_code
JOIN v_demand_trend t
    ON t.state_code = b.state_code AND t.year = 2024
ORDER BY t.excess_twh DESC;