How far the long tenor travels on days the front end jumps

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    multiIf(front_change <  0, 'front fell',
            front_change <  1, 'front up 0 to 1',
            front_change <  2, 'front up 1 to 2',
            front_change <  4, 'front up 2 to 4',
                               'front up 4 or more') AS front_move_band,
    round(avg(front_change), 2)                      AS front_change_pts,
    round(avg(back_change), 2)                       AS back_change_pts,
    count()                                          AS session_count
FROM
(
    SELECT
        (front_iv - prev_front) * 100 AS front_change,
        (back_iv  - prev_back)  * 100 AS back_change
    FROM
    (
        SELECT
            date,
            front_iv,
            back_iv,
            lagInFrame(front_iv) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_front,
            lagInFrame(back_iv)  OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_back
        FROM
        (
            SELECT
                date,
                avgIf(implied_volatility, days_to_expiry BETWEEN 7 AND 30)    AS front_iv,
                avgIf(implied_volatility, days_to_expiry BETWEEN 150 AND 300) AS back_iv
            FROM global_markets.options_greeks
            WHERE underlying_symbol = 'SPY'
              AND iv_converged = 1
              AND volume > 0
              AND (days_to_expiry BETWEEN 7 AND 30 OR days_to_expiry BETWEEN 150 AND 300)
              AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
              AND date >= today() - 730
            GROUP BY date
            HAVING countIf(days_to_expiry BETWEEN 7 AND 30) > 0
               AND countIf(days_to_expiry BETWEEN 150 AND 300) > 0
        )
    )
    WHERE prev_front > 0 AND prev_back > 0
)
GROUP BY front_move_band
ORDER BY avg(front_change)