3,256 answered market questions

WITH fridays AS
(
    SELECT
        date,
        toFloat64(volume) AS vol,
        (toDayOfMonth(date) BETWEEN 15 AND 21) AS is_third_friday,
        (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 >= '2022-12-01'
      AND date <  today()
      AND toDayOfWeek(date) = 5
)
SELECT
    toString(e.date)                     AS session_date,
    formatDateTime(e.date, '%b %Y')      AS session_label,
    round(any(e.vol) / 1e6, 1)           AS spy_volume_millions,
    round(any(e.vol) / avg(o.vol), 2)    AS vs_nearby_friday_ratio
FROM fridays AS e
CROSS JOIN fridays AS o
WHERE e.is_expiry = 1
  AND e.date >= '2023-01-01'
  AND o.is_third_friday = 0
  AND o.date BETWEEN e.date - 28 AND e.date + 28
GROUP BY e.date
ORDER BY e.date