roll_cycle

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
    toString(settlement)                                    AS settlement_date,
    concat('VX ', formatDateTime(contract_month, '%b %Y'))  AS vix_contract,
    toInt32(dateDiff('day', prior_settlement, settlement))  AS days_since_prior_settlement
FROM
(
    SELECT
        contract_month,
        settlement,
        lagInFrame(settlement) OVER (ORDER BY settlement ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_settlement
    FROM
    (
        SELECT
            a.contract_month AS contract_month,
            if(((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),
               addDays(a.ref_friday, -1),
               a.ref_friday)                                AS spx_anchor,
            addDays(spx_anchor, -30)                        AS wednesday_target,
            if(((wednesday_target <= toDate(arrayMax(t.session_days))) AND (NOT has(t.session_days, wednesday_target)))
                OR has(h.closed_days, wednesday_target),
               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 addMonths(toDate('2025-12-01'), toInt32(arrayJoin(range(13)))) AS contract_month
            )
        ) AS a
        CROSS JOIN traded AS t
        CROSS JOIN closures_ahead AS h
    )
)
WHERE prior_settlement >= toDate('2025-12-01')
ORDER BY settlement ASC