Average SPY option rho by time to expiration, indexed to the front month

SELECT
    b.dte_bucket                                AS dte_bucket,
    round(b.call_rho / f.front_call_rho, 2)     AS call_rho_ratio,
    round(b.put_rho  / f.front_put_rho,  2)     AS put_rho_ratio,
    b.contract_count                            AS contract_count
FROM
(
    SELECT
        multiIf(days_to_expiry <=  45, '21-45d',
                days_to_expiry <=  90, '46-90d',
                days_to_expiry <= 180, '91-180d',
                days_to_expiry <= 365, '181-365d',
                days_to_expiry <= 545, '366-545d',
                                       '546-760d')                     AS dte_bucket,
        min(days_to_expiry)                                            AS sort_key,
        avgIf(rho, startsWith(lower(toString(option_type)), 'c'))      AS call_rho,
        avgIf(rho, startsWith(lower(toString(option_type)), 'p'))      AS put_rho,
        count()                                                        AS contract_count
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2026-05-01'
      AND date <  '2026-08-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 21 AND 760
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.03
    GROUP BY dte_bucket
    HAVING countIf(startsWith(lower(toString(option_type)), 'c')) > 0
       AND countIf(startsWith(lower(toString(option_type)), 'p')) > 0
) AS b
CROSS JOIN
(
    SELECT
        avgIf(rho, startsWith(lower(toString(option_type)), 'c')) AS front_call_rho,
        avgIf(rho, startsWith(lower(toString(option_type)), 'p')) AS front_put_rho
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= '2026-05-01'
      AND date <  '2026-08-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 21 AND 45
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.03
) AS f
ORDER BY b.sort_key