2,170 answered market questions

WITH fridays AS
(
    SELECT
        date,
        toFloat64(volume) AS vol,
        (toMonth(date) IN (3, 6, 9, 12) AND toDayOfMonth(date) BETWEEN 15 AND 21) AS is_expiry
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2023-01-01'
      AND date <  today()
      AND toDayOfWeek(date) = 5
),
ordinary AS
(
    SELECT avg(vol) AS avg_ordinary_friday
    FROM fridays
    WHERE is_expiry = 0
)
SELECT
    toString(f.date)                         AS session_date,
    formatDateTime(f.date, '%b %Y')          AS session_label,
    round(f.vol / 1e6, 1)                    AS spy_volume_millions,
    round(f.vol / o.avg_ordinary_friday, 2)  AS vs_ordinary_friday_ratio
FROM fridays AS f
CROSS JOIN ordinary AS o
WHERE f.is_expiry = 1
ORDER BY f.date