A full trading day on the SPY tape, split by session window (recent full-length session)

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() - 21
          AND window_start < today() - 2
        GROUP BY d
        HAVING rth_bars = 390
    )
) AS session_day
SELECT
    toString(session_day)                     AS session_date,
    formatDateTime(session_day, '%W')         AS weekday,
    b.regular_session_bars                    AS regular_session_bars,
    b.first_bar_minute_of_day                 AS first_bar_minute_of_day,
    b.last_bar_minute_of_day                  AS last_bar_minute_of_day,
    b.first_bar_et                            AS first_bar_et,
    b.last_bar_et                             AS last_bar_et,
    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,
    t.midday_half_hour_pct                    AS midday_half_hour_pct
FROM
(
    SELECT
        countIf(et_min >= 570 AND et_min < 960)   AS regular_session_bars,
        min(et_min)                               AS first_bar_minute_of_day,
        max(et_min)                               AS last_bar_minute_of_day,
        formatDateTime(min(et_ts), '%H:%i')       AS first_bar_et,
        formatDateTime(max(et_ts), '%H:%i')       AS last_bar_et
    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 toDate(toTimeZone(window_start, 'America/New_York')) = session_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 < 960)) / sum(shares), 1)   AS regular_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 960 AND et_min < 990) / sum(shares), 1) AS post_close_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 990) / 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 >= 930 AND et_min < 960)) / sum(shares), 1)   AS last_30min_pct,
        round(100.0 * sumIf(shares, NOT is_close AND et_min >= 750 AND et_min < 780) / sum(shares), 1) AS midday_half_hour_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(session_day, 'America/New_York')
          AND sip_timestamp <  toDateTime(session_day + 1, 'America/New_York')
          AND NOT hasAny(conditions, [15, 16, 38])
    )
) AS t