Rule 204 close-out deadline for a short-sale fail, by trade date (US sessions, trailing weeks)

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(t_day)                     AS trade_date,
    formatDateTime(t_day, '%a')         AS trade_weekday,
    toString(s_day)                     AS settlement_date,
    toString(c_day)                     AS rule_204_deadline,
    formatDateTime(c_day, '%a')         AS deadline_weekday,
    dateDiff('day', t_day, c_day)       AS calendar_days_to_deadline
FROM
(
    SELECT
        session_date                                                                                AS t_day,
        anyLast(session_date) OVER (ORDER BY session_date ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s_day,
        anyLast(session_date) OVER (ORDER BY session_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS c_day,
        count()               OVER (ORDER BY session_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS sessions_in_frame
    FROM
    (
        SELECT DISTINCT date AS session_date
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= today() - 24
    )
)
WHERE sessions_in_frame = 3
ORDER BY t_day