3,256 answered market questions

WITH daily AS
(
    SELECT
        d,
        px / prev_px - 1 AS ret
    FROM
    (
        SELECT
            date                AS d,
            toFloat64(close)    AS px,
            lagInFrame(toFloat64(close)) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2015-12-31'
          AND date <= '2024-12-31'
    )
    WHERE prev_px > 0
)
SELECT
    toString(toYear(d))                                        AS year,
    round(avg(ret) / stddevPop(ret) * sqrt(252), 2)            AS spy_sharpe
FROM daily
GROUP BY year
ORDER BY year