Share of monthly volume by New York clock time, AAPL and KO

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
    et_time,
    round(100 * sumIf(shares, ticker = 'AAPL') / sum(sumIf(shares, ticker = 'AAPL')) OVER (), 3) AS aapl_share_pct,
    round(100 * sumIf(shares, ticker = 'KO')   / sum(sumIf(shares, ticker = 'KO'))   OVER (), 3) AS ko_share_pct
FROM
(
    SELECT
        ticker,
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
        toFloat64(volume)                                                                                           AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'KO')
      AND window_start >= today() - 45
      AND window_start <  today() - 2
      AND toHour(toTimeZone(window_start, 'America/New_York')) >= 4
      AND toHour(toTimeZone(window_start, 'America/New_York')) < 20
    UNION ALL
    SELECT
        ticker,
        '16:00'                                    AS et_time,
        toFloat64(maxIf(size, has(conditions, 8))) AS shares
    FROM global_markets.stocks_trades
    WHERE ticker IN ('AAPL', 'KO')
      AND sip_timestamp >= today() - 45
      AND sip_timestamp <  today() - 2
      AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
      AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) < 10
    GROUP BY ticker, toDate(toTimeZone(sip_timestamp, 'America/New_York'))
    HAVING countIf(has(conditions, 8)) > 0
)
GROUP BY et_time
ORDER BY et_time