window_volume

WITH
    spy_days AS
    (
        SELECT
            date,
            toYear(date)                                   AS y,
            toMonth(date)                                  AS m,
            argMax(toFloat64(volume), _ingest_time)        AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
        GROUP BY date
    ),
    yearly AS
    (
        SELECT
            y,
            avg(volume)                                    AS avg_volume
        FROM spy_days
        GROUP BY y
    ),
    ranked AS
    (
        SELECT
            date,
            y,
            m,
            volume,
            row_number() OVER (PARTITION BY y, m ORDER BY date DESC) AS sessions_from_end,
            row_number() OVER (PARTITION BY y, m ORDER BY date ASC)  AS sessions_from_start
        FROM spy_days
        WHERE m IN (12, 1)
    ),
    slots AS
    (
        SELECT
            if(r.m = 12, concat('Dec -', toString(r.sessions_from_end)),
                         concat('Jan +', toString(r.sessions_from_start)))  AS bucket,
            if(r.m = 12, toInt32(0) - toInt32(r.sessions_from_end),
                         toInt32(r.sessions_from_start))                    AS slot_order,
            r.y                                                             AS y,
            r.volume / yr.avg_volume * 100                                  AS volume_vs_year_pct
        FROM ranked AS r
        INNER JOIN yearly AS yr ON yr.y = r.y
        WHERE (r.m = 12 AND r.sessions_from_end <= 5)
           OR (r.m = 1 AND r.sessions_from_start <= 2)
    )
SELECT
    bucket,
    round(avg(volume_vs_year_pct), 1)                                       AS avg_volume_vs_year_pct,
    round(quantileDeterministic(0.5)(volume_vs_year_pct, toUInt32(y)), 1)   AS median_volume_vs_year_pct,
    count()                                                                 AS years_measured
FROM slots
GROUP BY bucket, slot_order
ORDER BY slot_order