Failed vs. successful Santa Claus windows and the calendar year that followed

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    spy_days AS
    (
        SELECT
            date,
            toYear(date)                                   AS y,
            toMonth(date)                                  AS m,
            argMax(toFloat64(close), _ingest_time)         AS close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
        GROUP BY date
    ),
    ranked AS
    (
        SELECT
            date,
            y,
            m,
            close,
            row_number() OVER (PARTITION BY y, m ORDER BY date DESC) AS sessions_from_end,
            row_number() OVER (PARTITION BY y, m ORDER BY date ASC)  AS sessions_from_start
        FROM spy_days
        WHERE m IN (12, 1)
    ),
    windows AS
    (
        SELECT
            toUInt16(if(m = 1, y - 1, y))                       AS year,
            toUInt16(year + 1)                                  AS next_year,
            anyIf(close, m = 12 AND sessions_from_end = 6)      AS base_close,
            anyIf(close, m = 12 AND sessions_from_end = 1)      AS december_close,
            anyIf(close, m = 1 AND sessions_from_start = 2)     AS end_close
        FROM ranked
        GROUP BY year
        HAVING countIf(m = 12 AND sessions_from_end = 6) = 1
           AND countIf(m = 1 AND sessions_from_start = 2) = 1
    ),
    year_ends AS
    (
        SELECT
            y,
            argMax(close, date)                                 AS year_end_close
        FROM spy_days
        WHERE m = 12
          AND date < toStartOfYear(today())
        GROUP BY y
    )
SELECT
    if(w.end_close < w.base_close, 'Window fell', 'Window rose')                          AS label,
    count()                                                                               AS windows_counted,
    countIf(n.year_end_close < w.december_close)                                          AS following_year_down,
    round(countIf(n.year_end_close < w.december_close) * 100 / count(), 1)                AS following_year_down_pct,
    round(avg((n.year_end_close / w.december_close - 1) * 100), 2)                        AS following_year_mean_pct,
    round(quantileDeterministic(0.5)((n.year_end_close / w.december_close - 1) * 100,
                                     toUInt32(w.year)), 2)                                AS following_year_median_pct
FROM windows AS w
INNER JOIN year_ends AS n ON n.y = w.next_year
GROUP BY label
ORDER BY label ASC