third_friday_gaps

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
    toString(d)                          AS expiration_date,
    formatDateTime(d, '%b %e, %Y')       AS expiration_label,
    round(gap_pct, 2)                    AS overnight_gap_pct,
    round(abs(gap_pct), 2)               AS abs_gap_pct,
    round(avg(abs(gap_pct)) OVER (), 2)  AS avg_abs_gap_pct
FROM
(
    SELECT
        d,
        100 * (toFloat64(open_px) - toFloat64(prev_close)) / toFloat64(prev_close) AS gap_pct
    FROM
    (
        SELECT
            date                                       AS d,
            open                                       AS open_px,
            lagInFrame(close) OVER (ORDER BY date ASC) AS prev_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2025-10-01'
          AND date <= today() - 2
    )
    WHERE toDayOfWeek(d) = 5
      AND toDayOfMonth(d) BETWEEN 15 AND 21
)
ORDER BY expiration_date