earnings_crush

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.

WITH atm_daily AS
(
    SELECT
        date,
        round(100 * avg(toFloat64(implied_volatility)), 1) AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'ZM'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 20 AND 45
      AND toFloat64(underlying_close) > 0
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
    GROUP BY date
),
seq AS
(
    SELECT
        date,
        iv_pct,
        toInt64(rn_raw) AS rn
    FROM
    (
        SELECT
            date,
            iv_pct,
            row_number() OVER (ORDER BY date) AS rn_raw
        FROM atm_daily
    )
),
events AS
(
    SELECT
        c.date                        AS crush_date,
        p.iv_pct                      AS iv_two_weeks_pct,
        b.iv_pct                      AS iv_peak_pct,
        c.iv_pct                      AS iv_after_pct,
        round(b.iv_pct - c.iv_pct, 1) AS crush_pct,
        row_number() OVER (PARTITION BY toStartOfQuarter(c.date) ORDER BY b.iv_pct - c.iv_pct DESC) AS quarter_rank
    FROM seq AS c
    INNER JOIN seq AS b ON b.rn = c.rn - 1
    INNER JOIN seq AS p ON p.rn = c.rn - 10
)
SELECT
    formatDateTime(crush_date, '%b %e, %Y') AS label,
    iv_two_weeks_pct,
    iv_peak_pct,
    iv_after_pct,
    crush_pct
FROM events
WHERE quarter_rank = 1
  AND crush_pct > 0
  AND crush_date >= today() - 760
ORDER BY crush_date