2,173 answered market questions

WITH
    monthly_px AS
    (
        SELECT
            toStartOfMonth(date)           AS m,
            argMax(toFloat64(close), date) AS month_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2004-12-01'
          AND date <  '2025-01-01'
        GROUP BY m
    ),
    prior_px AS
    (
        SELECT
            addMonths(m, 1) AS m,
            month_close     AS prev_close
        FROM monthly_px
    ),
    monthly_rf AS
    (
        SELECT
            toStartOfMonth(date)                                       AS m,
            pow(1 + avg(toFloat64(yield_3_month)) / 100, 1.0 / 12) - 1 AS rf_month
        FROM global_markets.treasury_yields
        WHERE date >= '2004-12-01'
          AND date <  '2025-01-01'
          AND yield_3_month IS NOT NULL
        GROUP BY m
    ),
    excess AS
    (
        SELECT
            cur.m                                             AS m,
            cur.month_close / prv.prev_close - 1              AS ret,
            rf.rf_month                                       AS rf_month,
            cur.month_close / prv.prev_close - 1 - rf.rf_month AS exc
        FROM monthly_px AS cur
        INNER JOIN prior_px AS prv ON prv.m = cur.m
        INNER JOIN monthly_rf AS rf ON rf.m = cur.m
    )
SELECT
    concat(toString(intDiv(count(), 12)), '-year')            AS horizon,
    round(100 * (exp(12 * avg(log(1 + exc))) - 1), 2)         AS excess_first_pct,
    round(100 * ((exp(12 * avg(log(1 + ret))) - 1)
               - (exp(12 * avg(log(1 + rf_month))) - 1)), 2)  AS annualize_first_pct,
    round(100 * ((exp(12 * avg(log(1 + ret))) - 1)
               - (exp(12 * avg(log(1 + rf_month))) - 1)
               - (exp(12 * avg(log(1 + exc))) - 1)), 3)       AS gap_pct
FROM excess
CROSS JOIN (SELECT arrayJoin([1, 3, 5, 10, 20]) AS years) AS hz
WHERE m >= subtractYears(toDate('2025-01-01'), years)
GROUP BY years
ORDER BY years