Month-end sessions vs all other sessions: SPY close concentration, January–June 2026

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    countIf(is_month_end) AS month_end_sessions,
    countIf(NOT is_month_end) AS other_sessions,
    round(avgIf(final_half_hour_pct, is_month_end), 1) AS month_end_final_half_pct,
    round(avgIf(final_half_hour_pct, NOT is_month_end), 1) AS other_final_half_pct,
    round(avgIf(final_half_hour_pct, is_month_end) - avgIf(final_half_hour_pct, NOT is_month_end), 1) AS gap_pp,
    round(avgIf(final_minute_pct, is_month_end), 1) AS month_end_final_min_pct,
    round(avgIf(final_minute_pct, NOT is_month_end), 1) AS other_final_min_pct
FROM (
    SELECT
        session_date,
        final_half_hour_pct,
        final_minute_pct,
        session_date = max(session_date) OVER (PARTITION BY toStartOfMonth(session_date)) AS is_month_end
    FROM (
        SELECT
            b.session_date AS session_date,
            100.0 * (b.half_hour_bars + p.closing_print) / (b.session_bars + p.closing_print) AS final_half_hour_pct,
            100.0 * (b.minute_bars + p.closing_print) / (b.session_bars + p.closing_print) AS final_minute_pct
        FROM (
            SELECT
                toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
                sumIf(toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') >= '15:30') AS half_hour_bars,
                sumIf(toFloat64(volume), formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') = '15:59') AS minute_bars,
                sum(toFloat64(volume)) AS session_bars
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND toDate(toTimeZone(window_start, 'America/New_York')) >= '2026-01-01'
              AND toDate(toTimeZone(window_start, 'America/New_York')) < '2026-07-01'
              AND formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') BETWEEN '09:30' AND '15:59'
            GROUP BY session_date
        ) AS b
        INNER JOIN (
            SELECT
                toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
                toFloat64(maxIf(size, has(conditions, 8))) AS closing_print
            FROM global_markets.stocks_trades
            WHERE ticker = 'SPY'
              AND sip_timestamp >= toDateTime('2026-01-01 00:00:00', 'America/New_York')
              AND sip_timestamp < toDateTime('2026-07-01 00:00:00', 'America/New_York')
              AND toHour(sip_timestamp, 'America/New_York') IN (13, 16)
              AND toMinute(sip_timestamp, 'America/New_York') < 10
            GROUP BY session_date
            HAVING countIf(has(conditions, 8)) > 0
        ) AS p ON p.session_date = b.session_date
    )
)