2,272 answered market questions

WITH tv AS (
    SELECT ticker,
           sum(toFloat64(close) * toFloat64(volume)) AS dollar_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING sum(toFloat64(close) * toFloat64(volume)) > 0
),
ranked AS (
    SELECT ticker, dollar_volume,
           row_number() OVER (ORDER BY dollar_volume DESC) AS rk
    FROM tv
)
SELECT multiIf(rk <= 10, 'top 10 names',
               rk <= 50, 'ranks 11 to 50',
               rk <= 200, 'ranks 51 to 200',
               rk <= 1000, 'ranks 201 to 1000',
               'ranks beyond 1000') AS liquidity_tier,
       count() AS tickers,
       round(sum(dollar_volume) / 1e9, 2) AS dollar_volume_bn,
       round(100 * sum(dollar_volume) / (SELECT sum(dollar_volume) FROM tv), 1) AS pct_of_dollar_volume
FROM ranked
GROUP BY liquidity_tier
ORDER BY min(rk)