3,256 answered market questions

WITH (
  SELECT max(toDate(sip_timestamp))
  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) = 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