2,170 answered market questions

WITH sessions AS
(
    SELECT DISTINCT date AS d
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2023-11-01'
),
month_ends AS
(
    SELECT
        toStartOfMonth(d) AS m,
        max(d)            AS anchor
    FROM sessions
    WHERE d >= '2024-01-01'
      AND d <  '2026-07-01'
    GROUP BY m
)
SELECT
    toString(me.m)                                            AS month,
    toString(me.anchor)                                       AS last_session,
    countIf(s.d >= me.anchor - 30 AND s.d <= me.anchor + 30)   AS sessions_in_window,
    61                                                        AS calendar_days_in_window
FROM month_ends AS me
CROSS JOIN sessions AS s
GROUP BY me.m, me.anchor
ORDER BY me.m