Top 5 gainers and top 5 decliners among names with $10M+ traded on June 30 (one reused-symbol listing excluded)

WITH per_ticker AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-06-30 00:00:00')) AS prior_close,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-06-30 00:00:00')) AS day_close,
        sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-06-30 00:00:00') AS day_dollar_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE (window_start >= '2026-06-29 13:30:00' AND window_start < '2026-06-29 20:00:00')
       OR (window_start >= '2026-06-30 13:30:00' AND window_start < '2026-06-30 20:00:00')
    GROUP BY ticker
    HAVING prior_close > 1 AND day_close > 0 AND day_dollar_volume >= 10000000 AND ticker NOT IN ('SPCX')
)
SELECT
    board,
    ticker,
    prior_close,
    day_close,
    pct_chg,
    round(abs(pct_chg), 2) AS abs_pct_chg,
    dollar_volume_m,
    if(ticker IN (SELECT ticker FROM global_markets.stocks_splits
                  WHERE execution_date >= '2026-06-29' AND execution_date <= '2026-06-30'), 1, 0) AS split_record_on_file
FROM (
    SELECT 'top gainers' AS board, ticker, round(prior_close, 2) AS prior_close, round(day_close, 2) AS day_close,
           round((day_close / prior_close - 1) * 100, 2) AS pct_chg, round(day_dollar_volume / 1e6, 0) AS dollar_volume_m
    FROM per_ticker
    ORDER BY (day_close / prior_close) DESC, ticker ASC
    LIMIT 5
    UNION ALL
    SELECT 'top decliners' AS board, ticker, round(prior_close, 2) AS prior_close, round(day_close, 2) AS day_close,
           round((day_close / prior_close - 1) * 100, 2) AS pct_chg, round(day_dollar_volume / 1e6, 0) AS dollar_volume_m
    FROM per_ticker
    ORDER BY (day_close / prior_close) ASC, ticker ASC
    LIMIT 5
)
ORDER BY if(board = 'top gainers', 0, 1) ASC, pct_chg DESC, ticker ASC