2,173 answered market questions

WITH raw AS (
    SELECT ticker,
           toTimeZone(window_start, 'America/New_York') AS et,
           close,
           volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'TLT', 'GLD', 'SLV', 'USO', 'UNG')
      AND window_start >= toDateTime('2026-07-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 00:00:00')
),
rth AS (
    SELECT ticker,
           volume AS shares,
           toFloat64(close) * volume AS dollars
    FROM raw
    WHERE (toHour(et) * 60 + toMinute(et)) BETWEEN 570 AND 959
)
SELECT ticker,
       round(sum(dollars) / sum(shares), 2) AS unit_size_usd,
       round(100 * sum(shares) / sum(sum(shares)) OVER (), 2) AS share_of_units_pct,
       round(100 * sum(dollars) / sum(sum(dollars)) OVER (), 2) AS share_of_dollars_pct
FROM rth
GROUP BY ticker
ORDER BY unit_size_usd DESC