q03_dam_vs_idm_premium.sql

-- ============================================
-- Q03: DAM vs IDM Premium by Hour-of-Day
-- ============================================
-- BUSINESS CONTEXT:
-- Intraday (IDM) prices trade after Day-Ahead (DAM) closes. The premium
-- IDM commands over DAM reflects intraday supply/demand imbalances and
-- speculative positioning. Understanding when this premium is highest
-- lets traders decide whether to remain in DAM or actively participate
-- in IDM to capture value — or hedge a short position.
--
-- QUESTION: Does IDM trade at a premium or discount to DAM, and how
--           does this pattern vary by hour-of-day?
--
-- DEMONSTRATES: Window functions with PARTITION BY, aggregate analytics
-- ============================================

WITH hourly_premium AS (
    SELECT
        f.ts,
        f.dam_price_try_mwh,
        f.idm_price_try_mwh,
        f.idm_price_try_mwh - f.dam_price_try_mwh      AS premium_try_mwh,
        (f.idm_price_try_mwh - f.dam_price_try_mwh)
            / NULLIF(f.dam_price_try_mwh, 0) * 100      AS premium_pct,
        c.hour,
        c.is_weekend
    FROM fact_market_hourly f
    JOIN dim_calendar        c ON f.ts = c.ts
    WHERE f.dam_price_try_mwh IS NOT NULL
      AND f.idm_price_try_mwh IS NOT NULL
),

by_hour AS (
    SELECT
        hour,
        is_weekend,
        ROUND(AVG(premium_try_mwh), 2)    AS avg_premium_try_mwh,
        ROUND(AVG(premium_pct), 2)        AS avg_premium_pct,
        ROUND(STDDEV_SAMP(premium_try_mwh), 2) AS std_premium,
        COUNT(*)                          AS obs_count,

        -- Rank hours by average premium within weekday/weekend group
        RANK() OVER (
            PARTITION BY is_weekend
            ORDER BY AVG(premium_try_mwh) DESC
        ) AS premium_rank
    FROM hourly_premium
    GROUP BY hour, is_weekend
)

SELECT
    hour,
    is_weekend,
    avg_premium_try_mwh,
    avg_premium_pct,
    std_premium,
    premium_rank,
    obs_count,
    CASE
        WHEN avg_premium_try_mwh > 20  THEN 'strong_premium'
        WHEN avg_premium_try_mwh > 5   THEN 'mild_premium'
        WHEN avg_premium_try_mwh > -5  THEN 'near_parity'
        ELSE                                 'discount'
    END AS premium_regime
FROM by_hour
ORDER BY is_weekend, hour;