q01_peak_offpeak_spread.sql

-- ============================================
-- Q01: Peak vs Off-Peak Spread Trend
-- ============================================
-- BUSINESS CONTEXT:
-- The peak/off-peak price spread directly drives the profitability of
-- flexible generation assets and battery storage. A widening spread
-- signals investment opportunity; a narrowing one signals saturation.
-- For a portfolio manager, monitoring this spread monthly is as fundamental
-- as watching absolute price levels.
--
-- QUESTION: How has the monthly peak/off-peak spread evolved over 4 years?
--
-- DEFINITION:
--   Peak hours    : 08:00 – 19:00 (inclusive)
--   Off-peak hours: 20:00 – 07:00
-- ============================================

WITH hourly_classified AS (
    SELECT
        f.ts,
        f.dam_price_try_mwh,
        c.year,
        c.month,
        CASE
            WHEN c.hour BETWEEN 8 AND 19 THEN 'peak'
            ELSE 'off_peak'
        END AS period_type
    FROM fact_market_hourly  f
    JOIN dim_calendar         c ON f.ts = c.ts
    WHERE f.dam_price_try_mwh IS NOT NULL
),

monthly_averages AS (
    SELECT
        year,
        month,
        ROUND(AVG(CASE WHEN period_type = 'peak'     THEN dam_price_try_mwh END), 2) AS avg_peak,
        ROUND(AVG(CASE WHEN period_type = 'off_peak' THEN dam_price_try_mwh END), 2) AS avg_offpeak,
        COUNT(CASE WHEN period_type = 'peak'     THEN 1 END) AS peak_hours,
        COUNT(CASE WHEN period_type = 'off_peak' THEN 1 END) AS offpeak_hours
    FROM hourly_classified
    GROUP BY year, month
)

SELECT
    year,
    month,
    avg_peak,
    avg_offpeak,
    ROUND(avg_peak - avg_offpeak, 2)           AS spread_try_mwh,
    ROUND((avg_peak - avg_offpeak)
          / NULLIF(avg_offpeak, 0) * 100, 1)   AS spread_pct,
    peak_hours,
    offpeak_hours
FROM monthly_averages
ORDER BY year, month;