The most recent early-close session on the SPY tape, split by session window

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.

WITH
(
    SELECT max(d)
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            countIf((toHour(toTimeZone(window_start, 'America/New_York')) * 60
                     + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
                AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
                     + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960) AS rth_bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars >= 150 AND rth_bars <= 240
    )
) AS half_day
SELECT
    toString(half_day)                          AS session_date,
    formatDateTime(half_day, '%W')              AS weekday,
    b.morning_session_bars                      AS morning_session_bars,
    b.last_bar_et                               AS last_bar_et,
    b.last_bar_minute_of_day                    AS last_bar_minute_of_day,
    t.total_shares_m                            AS total_shares_m,
    t.premarket_pct                             AS premarket_pct,
    t.regular_pct                               AS regular_pct,
    t.post_close_30min_pct                      AS post_close_30min_pct,
    t.evening_pct                               AS evening_pct,
    t.first_30min_pct                           AS first_30min_pct,
    t.last_30min_pct                            AS last_30min_pct,
    round(t.last_30min_pct - t.first_30min_pct, 1) AS close_minus_open_pct
FROM
(
    SELECT
        countIf(et_min >= 570 AND et_min < 780)     AS morning_session_bars,
        formatDateTime(max(et_ts), '%H:%i')         AS last_bar_et,
        max(et_min)                                 AS last_bar_minute_of_day
    FROM
    (
        SELECT
            toTimeZone(window_start, 'America/New_York') AS et_ts,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
            + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND toDate(toTimeZone(window_start, 'America/New_York')) = half_day
    )
) AS b
CROSS JOIN
(
    SELECT
        round(sum(shares) / 1e6, 1)                                                                   AS total_shares_m,
        round(100.0 * sumIf(shares, NOT is_close AND et_min < 570) / sum(shares), 1)                  AS premarket_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 570 AND et_min < 780)) / sum(shares), 1)   AS regular_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 780 AND et_min < 810) / sum(shares), 1) AS post_close_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 810) / sum(shares), 1)                 AS evening_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 570 AND et_min < 600) / sum(shares), 1) AS first_30min_pct,
        round(100.0 * sumIf(shares, is_close OR (et_min >= 750 AND et_min < 780)) / sum(shares), 1)   AS last_30min_pct
    FROM
    (
        SELECT
            toFloat64(size)    AS shares,
            has(conditions, 8) AS is_close,
            toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
            + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) AS et_min
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= toDateTime(half_day, 'America/New_York')
          AND sip_timestamp <  toDateTime(half_day + 1, 'America/New_York')
          AND NOT hasAny(conditions, [15, 16, 38])
    )
) AS t