2,309 answered market questions

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(date, m = 12 AND sessions_from_end = 6)       AS base_date,
            anyIf(date, m = 1 AND sessions_from_start = 2)      AS end_date,
            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
    concat('Dec ', toString(w.year), ' to Jan ', toString(w.next_year))                 AS label,
    concat(formatDateTime(w.base_date, '%b '), toString(toDayOfMonth(w.base_date)))     AS measured_from,
    concat(formatDateTime(w.end_date, '%b '), toString(toDayOfMonth(w.end_date)))       AS measured_to,
    round((w.end_close / w.base_close - 1) * 100, 2)                                    AS santa_window_pct,
    round((n.year_end_close / w.december_close - 1) * 100, 2)                           AS following_year_pct
FROM windows AS w
INNER JOIN year_ends AS n ON n.y = w.next_year
ORDER BY santa_window_pct ASC
LIMIT 5