Beta and R-squared against SPY: daily returns versus weekly, twelve months to Aug 21 2026

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','AAPL','MSFT','NVDA','TSLA','COIN','KO','JNJ','XOM','GLD','TLT')
          AND date >= '2025-08-22'
          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
    ),
    weekly_px AS
    (
        SELECT
            ticker,
            toMonday(date)         AS week_start,
            argMax(close_px, date) AS week_close
        FROM px
        GROUP BY ticker, week_start
    ),
    weekly_ret AS
    (
        SELECT
            ticker,
            week_start,
            week_close / lagInFrame(week_close) OVER (PARTITION BY ticker ORDER BY week_start ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM weekly_px
    ),
    daily_beta AS
    (
        SELECT
            s.ticker                                           AS symbol,
            round(covarSamp(s.ret, i.ret) / varSamp(i.ret), 2) AS beta_daily,
            round(pow(corr(s.ret, i.ret), 2), 2)               AS r_squared,
            count()                                            AS return_count
        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 != 'SPY' AND isFinite(s.ret)
        GROUP BY symbol
    ),
    weekly_beta AS
    (
        SELECT
            s.ticker                                           AS symbol,
            round(covarSamp(s.ret, i.ret) / varSamp(i.ret), 2) AS beta_weekly
        FROM weekly_ret AS s
        INNER JOIN
        (
            SELECT week_start, ret FROM weekly_ret WHERE ticker = 'SPY' AND isFinite(ret)
        ) AS i ON i.week_start = s.week_start
        WHERE s.ticker != 'SPY' AND isFinite(s.ret)
        GROUP BY symbol
    )
SELECT
    d.symbol       AS symbol,
    d.beta_daily   AS beta_daily,
    w.beta_weekly  AS beta_weekly,
    d.r_squared    AS r_squared,
    d.return_count AS return_count
FROM daily_beta AS d
INNER JOIN weekly_beta AS w ON w.symbol = d.symbol
ORDER BY beta_daily DESC