Beta, price ratio, and SPY-share equivalent per share held

WITH
    sessions AS
    (
        SELECT
            ticker                                               AS ticker,
            toDate(toTimeZone(window_start, 'America/New_York')) AS d,
            toFloat64(argMax(close, window_start))               AS px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('SPY', 'NVDA', 'AAPL', 'MSFT', 'XOM', 'JNJ', 'KO')
          AND window_start >= toDateTime('2025-07-01 04:00:00')
          AND window_start <  toDateTime('2026-07-01 04:00:00')
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) <  960
        GROUP BY ticker, d
    ),
    steps AS
    (
        SELECT
            ticker,
            d,
            px,
            any(px) OVER (PARTITION BY ticker ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_px
        FROM sessions
    ),
    daily_ret AS
    (
        SELECT ticker, d, (px / prev_px) - 1 AS r
        FROM steps
        WHERE prev_px > 0
    ),
    bench AS
    (
        SELECT d, r AS spy_r
        FROM daily_ret
        WHERE ticker = 'SPY'
    ),
    betas AS
    (
        SELECT
            s.ticker                                 AS ticker,
            covarPop(s.r, b.spy_r) / varPop(b.spy_r) AS beta_vs_spy,
            1                                        AS k
        FROM daily_ret AS s
        INNER JOIN bench AS b ON b.d = s.d
        GROUP BY s.ticker
    ),
    prices AS
    (
        SELECT ticker, argMax(px, d) AS close_px
        FROM sessions
        GROUP BY ticker
    ),
    spy_price AS
    (
        SELECT argMax(px, d) AS spy_close, 1 AS k
        FROM sessions
        WHERE ticker = 'SPY'
    )
SELECT
    betas.ticker                                                        AS ticker,
    round(betas.beta_vs_spy, 2)                                         AS beta_vs_spy,
    round(prices.close_px / spy_price.spy_close, 3)                     AS price_vs_spy,
    round(betas.beta_vs_spy * prices.close_px / spy_price.spy_close, 3) AS spy_shares_per_share
FROM betas
INNER JOIN prices ON prices.ticker = betas.ticker
INNER JOIN spy_price ON spy_price.k = betas.k
ORDER BY beta_vs_spy DESC