Period-over-Period Comparisons — WoW, MoM, YoY in One Query

Period-over-period (PoP) analysis answers: is this week better or worse than last week? We build WoW, MoM, and YoY comparisons in a single query using LAG with date offsets.

-- Daily energy consumption with WoW and 4-week-ago comparison
WITH daily_kwh AS (
    SELECT
        meter_id,
        building,
        read_at::DATE                                   AS reading_date,
        SUM(kwh)                                        AS daily_kwh
    FROM ts_energy_readings
    GROUP BY meter_id, building, read_at::DATE
),
with_prior AS (
    SELECT
        meter_id,
        building,
        reading_date,
        daily_kwh,
        LAG(daily_kwh, 7) OVER (
            PARTITION BY meter_id ORDER BY reading_date
        )                                               AS kwh_7d_ago,
        LAG(daily_kwh, 28) OVER (
            PARTITION BY meter_id ORDER BY reading_date
        )                                               AS kwh_28d_ago,
        AVG(daily_kwh) OVER (
            PARTITION BY meter_id
            ORDER BY reading_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )                                               AS rolling_7d_avg
    FROM daily_kwh
)
SELECT
    building,
    reading_date,
    ROUND(daily_kwh, 2)                                 AS kwh_today,
    ROUND(kwh_7d_ago, 2)                                AS kwh_1w_ago,
    ROUND(kwh_28d_ago, 2)                               AS kwh_4w_ago,
    ROUND(rolling_7d_avg, 2)                            AS rolling_avg_7d,
    ROUND(100.0 * (daily_kwh - kwh_7d_ago)
          / NULLIF(kwh_7d_ago, 0), 1)                   AS wow_pct,
    ROUND(100.0 * (daily_kwh - kwh_28d_ago)
          / NULLIF(kwh_28d_ago, 0), 1)                  AS vs_4w_ago_pct
FROM with_prior
WHERE reading_date >= CURRENT_DATE - 14
  AND kwh_7d_ago IS NOT NULL
ORDER BY building, reading_date;