A three-leg book, raw delta against beta weighted delta

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', 'AAPL', '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
        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, 1 AS k
        FROM sessions
        GROUP BY ticker
    ),
    spy_price AS
    (
        SELECT argMax(px, d) AS spy_close, 1 AS k
        FROM sessions
        WHERE ticker = 'SPY'
    ),
    opt_delta AS
    (
        SELECT
            underlying_symbol                       AS ticker,
            if(toFloat64(delta) > 0, 'call', 'put') AS kind,
            avg(toFloat64(delta))                   AS per_share_delta
        FROM global_markets.options_greeks
        WHERE date >= toDate('2026-06-30')
          AND date <  toDate('2026-07-01')
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 25 AND 40
          AND (
                (underlying_symbol = 'AAPL' AND toFloat64(delta) > 0
                 AND toFloat64(strike_price) / toFloat64(underlying_close) BETWEEN 1.03 AND 1.07)
             OR (underlying_symbol = 'KO' AND toFloat64(delta) < 0
                 AND toFloat64(strike_price) / toFloat64(underlying_close) BETWEEN 0.97 AND 1.03)
          )
        GROUP BY ticker, kind
    ),
    legs AS
    (
        SELECT
            tupleElement(leg, 1) AS leg_no,
            tupleElement(leg, 2) AS position,
            tupleElement(leg, 3) AS ticker,
            tupleElement(leg, 4) AS kind,
            tupleElement(leg, 5) AS qty,
            tupleElement(leg, 6) AS multiplier
        FROM
        (
            SELECT arrayJoin([
                (1, 'Long 300 AAPL',      'AAPL', 'stock', 300., 1.),
                (2, 'Short 3 AAPL calls', 'AAPL', 'call',   -3., 100.),
                (3, 'Long 5 KO puts',     'KO',   'put',     5., 100.)
            ]) AS leg
        )
    )
SELECT
    position                                                                                          AS position,
    round(raw_share_delta)                                                                            AS raw_share_delta,
    round(beta_vs_spy, 2)                                                                             AS beta_vs_spy,
    round(bw_delta)                                                                                   AS beta_weighted_delta,
    round(sum(bw_delta) OVER (ORDER BY leg_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))       AS running_book_delta,
    round(sum(bw_delta) OVER (ORDER BY leg_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * spy_close / 100.) AS dollars_per_1pct_spy
FROM
(
    SELECT
        l.leg_no                                                            AS leg_no,
        l.position                                                          AS position,
        l.qty * l.multiplier * if(l.kind = 'stock', 1., o.per_share_delta)  AS raw_share_delta,
        bt.beta_vs_spy                                                      AS beta_vs_spy,
        sp.spy_close                                                        AS spy_close,
        raw_share_delta * bt.beta_vs_spy * pr.close_px / sp.spy_close       AS bw_delta
    FROM legs AS l
    LEFT  JOIN opt_delta AS o  ON o.ticker = l.ticker AND o.kind = l.kind
    INNER JOIN betas     AS bt ON bt.ticker = l.ticker
    INNER JOIN prices    AS pr ON pr.ticker = l.ticker
    INNER JOIN spy_price AS sp ON sp.k = pr.k
)
ORDER BY leg_no