Where the KO/PEP spread sat twenty sessions later, by starting z score (2019-2025)

WITH
    daily AS (
        SELECT
            date,
            anyIf(toFloat64(close), ticker = 'PEP') AS pep,
            anyIf(toFloat64(close), ticker = 'KO')  AS ko
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('KO', 'PEP')
          AND date BETWEEN '2018-01-01' AND '2025-12-31'
        GROUP BY date
        HAVING pep > 0 AND ko > 0
    ),
    spread AS (
        SELECT
            date,
            log(pep / ko) AS log_ratio
        FROM daily
    ),
    scored AS (
        SELECT
            date,
            (log_ratio - avg(log_ratio) OVER (
                 ORDER BY date ROWS BETWEEN 62 PRECEDING AND CURRENT ROW))
            / stddevSampStable(log_ratio) OVER (
                 ORDER BY date ROWS BETWEEN 62 PRECEDING AND CURRENT ROW) AS z
        FROM spread
    ),
    horizon AS (
        SELECT
            date,
            z,
            leadInFrame(z, 20) OVER (
                ORDER BY date ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING
            ) AS z_fwd
        FROM scored
        WHERE isFinite(z)
    )
SELECT
    multiIf(z < -2, 'z below -2',
            z < -1, 'z -2 to -1',
            z <  0, 'z -1 to 0',
            z <  1, 'z 0 to 1',
            z <  2, 'z 1 to 2',
                    'z above 2')  AS z_bucket,
    round(avg(z), 2)              AS avg_z_start,
    round(avg(z_fwd), 2)          AS avg_z_20d_later,
    count()                       AS episode_count
FROM horizon
WHERE date >= '2019-01-01'
  AND date <= '2025-11-15'
  AND isFinite(z_fwd)
GROUP BY z_bucket
ORDER BY avg_z_start