האם מניה עולה אחרי פיצול?

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

WITH
cohort AS (
    SELECT ticker, min(execution_date) AS ex
    FROM global_markets.stocks_splits
    WHERE adjustment_type IN ('forward_split', 'stock_dividend') AND split_to >= 2 * split_from
      AND execution_date >= '2016-01-01' AND execution_date <= '2025-12-31' AND ticker != 'SPCX'
    GROUP BY ticker, execution_date
),
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 >= '2015-06-01 00:00:00' AND window_start < '2026-02-01 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'),
fwd AS (
    SELECT ex,
        (prices[i0 - 1] / prices[i0 - 1 - 126] - 1) * 100 AS run_up,
        (prices[i0 + 63] / prices[i0] - 1) * 100 AS ret,
        (sp[j0 + 63] / sp[j0] - 1) * 100 AS spy_ret
    FROM (
        SELECT c.ex AS ex, indexOf(s.days, c.ex) AS i0, indexOf(spy.sd, c.ex) AS j0,
               s.prices AS prices, spy.sp AS sp
        FROM cohort c INNER JOIN series s ON s.ticker = c.ticker CROSS JOIN spy
    )
    WHERE i0 > 130 AND length(prices) >= i0 + 63 AND prices[i0] > 0 AND prices[i0 - 1] >= 30
)
SELECT multiIf(toYear(ex) <= 2019, '2016-2019', toYear(ex) <= 2023, '2020-2023', '2024-2025') AS period,
    count() AS splits,
    round(median(run_up), 1) AS median_runup_before_pct,
    round(median(ret), 1) AS median_after_3mo_pct,
    round(median(spy_ret), 1) AS median_spy_3mo_pct,
    round(median(ret - spy_ret), 1) AS median_gap_vs_spy_pct,
    round(100.0 * countIf(ret > spy_ret) / count(), 0) AS pct_beat_spy
FROM fwd GROUP BY period ORDER BY period