This calendar year's closures: already passed (counted from the tape) plus still ahead (from the calendar)

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 day_bars AS (
    SELECT cal.day AS day, ifNull(t.bars, 0) AS bars
    FROM (SELECT makeDate(toYear(today()), 1, 1) + arrayJoin(range(370)) AS day) AS cal
    LEFT JOIN (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime(makeDate(toYear(today()), 1, 1), 'America/New_York')
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
        GROUP BY d
    ) AS t ON t.d = cal.day
    WHERE toDayOfWeek(cal.day) BETWEEN 1 AND 5
      AND cal.day <= today() - 4
),
past AS (
    SELECT countIf(bars = 0) AS past_full, countIf(bars BETWEEN 120 AND 330) AS past_early
    FROM day_bars
),
ahead AS (
    SELECT countDistinctIf(date, status = 'closed') AS ahead_full,
           countDistinctIf(date, status = 'early-close') AS ahead_early
    FROM global_markets.stocks_market_holidays
    WHERE date > today() - 4 AND toYear(date) = toYear(today())
)
SELECT category, already_passed, still_ahead, already_passed + still_ahead AS total, toString(toYear(today())) AS calendar_year
FROM (
    SELECT 'full closure' AS category, past.past_full AS already_passed, ahead.ahead_full AS still_ahead, 1 AS ord FROM past, ahead
    UNION ALL
    SELECT 'early close (1:00 pm)' AS category, past.past_early AS already_passed, ahead.ahead_early AS still_ahead, 2 AS ord FROM past, ahead
)
ORDER BY ord