Advancers, decliners, and new quarterly highs vs lows among tickers with at least $1M traded on July 1

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 per_ticker AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-01 00:00:00')) AS prior_close,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-01 00:00:00')) AS day_close,
        maxIf(toFloat64(high), window_start < '2026-07-01 00:00:00') AS quarter_high,
        minIf(toFloat64(low), window_start < '2026-07-01 00:00:00') AS quarter_low,
        maxIf(toFloat64(high), window_start >= '2026-07-01 00:00:00') AS day_high,
        minIf(toFloat64(low), window_start >= '2026-07-01 00:00:00') AS day_low,
        sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-01 00:00:00') AS day_dollar_volume,
        countIf(window_start < '2026-07-01 00:00:00') AS quarter_bars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ((window_start >= '2026-04-01 13:30:00' AND window_start < '2026-06-30 20:00:00')
        OR (window_start >= '2026-07-01 13:30:00' AND window_start < '2026-07-01 20:00:00'))
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY ticker
)
SELECT
    countIf(day_close > prior_close AND day_dollar_volume >= 1000000) AS advancers,
    countIf(day_close < prior_close AND day_dollar_volume >= 1000000) AS decliners,
    countIf(day_close = prior_close AND day_dollar_volume >= 1000000) AS unchanged,
    round(100.0 * countIf(day_close > prior_close AND day_dollar_volume >= 1000000)
        / countIf(day_dollar_volume >= 1000000), 1) AS advancer_pct,
    countIf(day_dollar_volume >= 1000000 AND quarter_bars >= 1000 AND day_high > quarter_high) AS new_quarter_highs,
    countIf(day_dollar_volume >= 1000000 AND quarter_bars >= 1000 AND day_low < quarter_low) AS new_quarter_lows,
    round(countIf(day_dollar_volume >= 1000000 AND quarter_bars >= 1000 AND day_high > quarter_high)
        / countIf(day_dollar_volume >= 1000000 AND quarter_bars >= 1000 AND day_low < quarter_low), 1) AS highs_per_low,
    countIf(day_dollar_volume >= 1000000 AND quarter_bars >= 1000) AS names_with_a_full_quarter,
    countIf(day_dollar_volume >= 1000000) AS liquid_tickers,
    count() AS tickers_traded_both_sessions,
    count() - countIf(day_dollar_volume >= 1000000) AS dropped_by_liquidity_filter,
    countIf(day_dollar_volume >= 1000000 AND quarter_bars < 1000) AS dropped_short_quarter_history
FROM per_ticker
WHERE prior_close > 0 AND day_close > 0