SPY: average share of full-day volume completed by each clock time (last 20 sessions)

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 per_min AS (
    SELECT toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS et_date,
           formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i') AS et_min,
           sum(toFloat64(size)) AS v
    FROM global_markets.stocks_trades
    WHERE ticker = 'SPY'
      AND sip_timestamp >= now() - INTERVAL 45 DAY
      AND toDate(toTimeZone(sip_timestamp, 'America/New_York')) < toDate(toTimeZone(now(), 'America/New_York'))
      AND NOT hasAny(conditions, [15, 16, 38])
    GROUP BY et_date, et_min
),
last20 AS (
    SELECT DISTINCT et_date FROM per_min ORDER BY et_date DESC LIMIT 20
)
SELECT checkpoint AS et_checkpoint,
       round(avg(share) * 100, 1) AS avg_pct_of_day_volume_done
FROM (
    SELECT et_date,
           checkpoint,
           sumIf(v, et_min < checkpoint) / sum(v) AS share
    FROM (
        SELECT et_date, et_min, v, arrayJoin(['10:00', '12:00', '14:00', '15:30', '16:00']) AS checkpoint
        FROM per_min
        WHERE et_date IN (SELECT et_date FROM last20)
    )
    GROUP BY et_date, checkpoint
)
GROUP BY checkpoint
ORDER BY checkpoint