SPY daily bar volume vs. the regular-session slice (trailing month)

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
    toString(daily.date)                                                                AS session_date,
    concat(formatDateTime(daily.date, '%b'), ' ', toString(toDayOfMonth(daily.date)))   AS session_label,
    round(toFloat64(daily.day_volume) / 1e6, 1)                                         AS daily_bar_millions,
    round(toFloat64(intraday.regular_volume) / 1e6, 1)                                  AS regular_session_millions,
    round(100 * (toFloat64(daily.day_volume) - toFloat64(intraday.regular_volume))
              / toFloat64(daily.day_volume), 2)                                         AS outside_regular_pct
FROM
(
    SELECT
        date,
        max(volume) AS day_volume
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= today() - 30
      AND date <  today() - 2
    GROUP BY date
) AS daily
INNER JOIN
(
    SELECT
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS et_date,
        sumIf(toFloat64(size), has(conditions, 8)
              OR (toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
                  + toMinute(toTimeZone(sip_timestamp, 'America/New_York'))) BETWEEN 570 AND 959) AS regular_volume
    FROM global_markets.stocks_trades
    WHERE ticker = 'SPY'
      AND sip_timestamp >= today() - 30
      AND sip_timestamp <  today() - 2
      AND NOT hasAny(conditions, [15, 16, 38])
    GROUP BY et_date
    HAVING countIf(has(conditions, 8)) > 0
) AS intraday ON intraday.et_date = daily.date
ORDER BY daily.date