Daily turnover in the top ten movers: fixed 36 name large-cap list, July 2026

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           round((argMax(close, window_start) / argMin(open, window_start) - 1) * 100, 2) AS move_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','TSLA','AVGO','JPM','XOM',
                     'JNJ','WMT','PG','KO','HD','CVX','MRK','PEP','COST','CSCO',
                     'ORCL','CRM','AMD','NFLX','DIS','BA','CAT','IBM','T','VZ',
                     'PFE','NKE','MCD','UNH','BAC','QCOM')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-07-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
ranked AS (
    SELECT d,
           ticker,
           row_number() OVER (PARTITION BY d ORDER BY abs(move_pct) DESC, ticker ASC) AS rnk
    FROM daily
),
top_ten AS (
    SELECT d, arraySort(groupArray(ticker)) AS names
    FROM ranked
    WHERE rnk <= 10
    GROUP BY d
),
paired AS (
    SELECT d,
           names,
           lagInFrame(names) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_names
    FROM top_ten
)
SELECT formatDateTime(d, '%Y-%m-%d') AS date,
       length(arrayIntersect(names, prior_names)) AS carried_over,
       10 - length(arrayIntersect(names, prior_names)) AS new_to_the_list
FROM paired
WHERE length(prior_names) = 10
ORDER BY d ASC