AAPL top of book by half hour (ET), latest session: NBBO updates and displayed size at the best bid and ask as a share of the peak bucket

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(toDate(sip_timestamp, 'America/New_York'))
  FROM global_markets.cache_stocks_quotes
  WHERE ticker = 'AAPL' AND sip_timestamp >= now() - INTERVAL 7 DAY
) AS last_session
SELECT
  et_time,
  quote_update_count,
  round(100 * median_bid_size / max(median_bid_size) OVER (), 0) AS displayed_bid_pct_of_peak,
  round(100 * median_ask_size / max(median_ask_size) OVER (), 0) AS displayed_ask_pct_of_peak
FROM (
  SELECT
    formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
    count() AS quote_update_count,
    quantileDeterministic(0.5)(toFloat64(bid_size), toUInt64(sip_timestamp)) AS median_bid_size,
    quantileDeterministic(0.5)(toFloat64(ask_size), toUInt64(sip_timestamp)) AS median_ask_size
  FROM global_markets.cache_stocks_quotes
  WHERE ticker = 'AAPL'
    AND sip_timestamp >= now() - INTERVAL 7 DAY
    AND toDate(sip_timestamp, 'America/New_York') = last_session
    AND bid_price > 0
    AND ask_price > bid_price
    AND bid_size > 0
    AND ask_size > 0
  GROUP BY et_time
)
ORDER BY et_time