2,178 answered market questions

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','NVDA')
          AND date >= '2021-08-01'
          AND date <= '2026-08-21'
    ),
    daily_ret AS
    (
        SELECT
            ticker,
            date,
            close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM px
    ),
    paired AS
    (
        SELECT
            s.date AS d,
            s.ret  AS stock_ret,
            i.ret  AS index_ret
        FROM daily_ret AS s
        INNER JOIN
        (
            SELECT date, ret FROM daily_ret WHERE ticker = 'SPY' AND isFinite(ret)
        ) AS i ON i.date = s.date
        WHERE s.ticker = 'NVDA' AND isFinite(s.ret)
    ),
    anchors AS
    (
        SELECT DISTINCT toStartOfMonth(d) AS anchor
        FROM paired
        WHERE d >= '2022-09-01'
    )
SELECT
    formatDateTime(a.anchor, '%Y-%m')                                    AS month,
    formatDateTime(a.anchor, '%b %Y')                                    AS month_label,
    round(covarSamp(p.stock_ret, p.index_ret) / varSamp(p.index_ret), 2) AS rolling_beta_1y,
    round(pow(corr(p.stock_ret, p.index_ret), 2), 2)                     AS r_squared
FROM anchors AS a, paired AS p
WHERE p.d < a.anchor
  AND p.d >= subtractYears(a.anchor, 1)
GROUP BY a.anchor
ORDER BY a.anchor