What the next session did: 36 large caps bucketed by the prior day's move, Aug 2024 to Jul 2026

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           (argMax(close, window_start) / argMin(open, window_start) - 1) * 100 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('2024-08-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
),
paired AS (
    SELECT ticker,
           d,
           move_pct,
           leadInFrame(move_pct) OVER (PARTITION BY ticker ORDER BY d ASC
                                       ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_move_pct,
           row_number() OVER (PARTITION BY ticker ORDER BY d ASC) AS rn,
           count() OVER (PARTITION BY ticker) AS session_total
    FROM daily
),
labelled AS (
    SELECT multiIf(move_pct <= -3, 'fell 3% or more',
                   move_pct <= -1, 'fell 1% to 3%',
                   move_pct < 1, 'moved less than 1%',
                   move_pct < 3, 'rose 1% to 3%',
                   'rose 3% or more') AS prior_day_bucket,
           multiIf(move_pct <= -3, 1,
                   move_pct <= -1, 2,
                   move_pct < 1, 3,
                   move_pct < 3, 4,
                   5) AS bucket_rank,
           move_pct,
           next_move_pct
    FROM paired
    WHERE rn < session_total
)
SELECT prior_day_bucket,
       count() AS ticker_day_count,
       round(avg(move_pct), 2) AS prior_day_move_pct,
       round(avg(next_move_pct), 3) AS next_day_move_pct,
       round(100 * countIf((move_pct > 0) = (next_move_pct > 0)) / count(), 1) AS same_direction_pct
FROM labelled
GROUP BY prior_day_bucket, bucket_rank
ORDER BY bucket_rank ASC