The best-known recent stock splits: the six months before vs the three months after

WITH
cohort AS (
    SELECT ticker, max(execution_date) AS ex,
           argMax(concat(toString(split_to), '-for-', toString(split_from)), execution_date) AS ratio
    FROM global_markets.stocks_splits
    WHERE ticker IN ('NVDA', 'AVGO', 'NFLX', 'AMZN', 'GOOGL', 'AAPL', 'TSLA')
      AND split_to >= 2 * split_from
      AND adjustment_type IN ('forward_split', 'stock_dividend')
      AND execution_date >= '2020-01-01' AND execution_date <= '2026-01-31'
    GROUP BY ticker
),
bars AS (
    SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           toFloat64(argMax(close, window_start)) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE (ticker IN (SELECT ticker FROM cohort) OR ticker = 'SPY')
      AND window_start >= '2019-06-01 00:00:00' AND window_start < '2026-07-14 00:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, d
),
series AS (
    SELECT ticker,
           arrayMap(x -> x.1, arraySort(x -> x.1, groupArray((d, px)))) AS days,
           arrayMap(x -> x.2, arraySort(x -> x.1, groupArray((d, px)))) AS prices
    FROM bars GROUP BY ticker
),
spy AS (SELECT days AS sd, prices AS sp FROM series WHERE ticker = 'SPY')
SELECT c.ticker AS ticker,
       c.ratio AS ratio,
       toString(c.ex) AS ex_date,
       round((s.prices[indexOf(s.days, c.ex) - 1] / s.prices[indexOf(s.days, c.ex) - 1 - 126] - 1) * 100, 1) AS run_up_before_pct,
       round((s.prices[indexOf(s.days, c.ex) + 63] / s.prices[indexOf(s.days, c.ex)] - 1) * 100, 1) AS after_3mo_pct,
       round((spy.sp[indexOf(spy.sd, c.ex) + 63] / spy.sp[indexOf(spy.sd, c.ex)] - 1) * 100, 1) AS spy_3mo_pct
FROM cohort c INNER JOIN series s ON s.ticker = c.ticker CROSS JOIN spy
WHERE indexOf(s.days, c.ex) > 130 AND length(s.prices) >= indexOf(s.days, c.ex) + 63
ORDER BY run_up_before_pct DESC