walk_back

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
    traded AS
    (
        SELECT groupArray(session_day) AS session_days
        FROM
        (
            SELECT date AS session_day
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date >= toDate('2014-11-01')
            GROUP BY session_day
        )
    ),
    closures_ahead AS
    (
        SELECT groupArray(date) AS closed_days
        FROM global_markets.stocks_market_holidays
        WHERE status = 'closed'
    )
SELECT
    concat('VX ', formatDateTime(contract_month, '%b %Y'))  AS vix_contract,
    formatDateTime(ref_friday, '%a %b %e, %Y')              AS next_month_third_friday,
    if(friday_closed, 'exchange holiday', 'trading day')    AS friday_status,
    formatDateTime(spx_anchor, '%a %b %e, %Y')              AS counted_back_from,
    formatDateTime(wednesday_target, '%a %b %e, %Y')        AS thirty_days_earlier,
    if(target_closed, 'exchange holiday', 'trading day')    AS that_day_status,
    formatDateTime(settlement, '%a %b %e, %Y')              AS final_settlement_date
FROM
(
    SELECT
        a.contract_month AS contract_month,
        a.ref_friday     AS ref_friday,
        ((a.ref_friday <= toDate(arrayMax(t.session_days))) AND (NOT has(t.session_days, a.ref_friday)))
            OR has(h.closed_days, a.ref_friday)             AS friday_closed,
        if(friday_closed, addDays(a.ref_friday, -1), a.ref_friday) AS spx_anchor,
        addDays(spx_anchor, -30)                            AS wednesday_target,
        ((wednesday_target <= toDate(arrayMax(t.session_days))) AND (NOT has(t.session_days, wednesday_target)))
            OR has(h.closed_days, wednesday_target)         AS target_closed,
        if(target_closed, addDays(wednesday_target, -1), wednesday_target) AS settlement
    FROM
    (
        SELECT
            contract_month,
            addMonths(contract_month, 1)                                             AS ref_month,
            addDays(ref_month, ((5 - toInt32(toDayOfWeek(ref_month)) + 7) % 7) + 14) AS ref_friday
        FROM
        (
            SELECT toDate(arrayJoin(['2026-01-01', '2026-12-01', '2026-05-01', '2024-06-01'])) AS contract_month
        )
    ) AS a
    CROSS JOIN traded AS t
    CROSS JOIN closures_ahead AS h
)
ORDER BY multiIf(contract_month = toDate('2026-01-01'), 1,
                 contract_month = toDate('2026-12-01'), 2,
                 contract_month = toDate('2026-05-01'), 3,
                 4) ASC