Is the Stock Market Open Today?

WITH
    toTimeZone(now(), 'America/New_York') AS now_et,
    toDate(now_et) AS today_et,
    toHour(now_et) * 60 + toMinute(now_et) AS min_now,
    (SELECT countDistinct(date) FROM global_markets.stocks_market_holidays
      WHERE date = toDate(toTimeZone(now(), 'America/New_York')) AND status = 'closed') AS today_full_holiday,
    (SELECT countDistinct(date) FROM global_markets.stocks_market_holidays
      WHERE date = toDate(toTimeZone(now(), 'America/New_York')) AND status = 'early-close') AS today_early,
    (SELECT any(name) FROM global_markets.stocks_market_holidays
      WHERE date = toDate(toTimeZone(now(), 'America/New_York'))) AS today_holiday_name,
    if(today_early = 1, 780, 960) AS close_min,
    if(today_early = 1, 1020, 1200) AS after_hours_end,
    (SELECT min(d) FROM (SELECT toDate(toTimeZone(now(), 'America/New_York')) + arrayJoin(range(1, 11)) AS d)
      WHERE toDayOfWeek(d) <= 5
        AND d NOT IN (SELECT date FROM global_markets.stocks_market_holidays WHERE status = 'closed')) AS next_sess_after_today,
    toDayOfWeek(today_et) <= 5 AND today_full_holiday = 0 AS today_is_session
SELECT
    toString(today_et) AS date_checked,
    formatDateTime(today_et, '%W') AS weekday,
    formatDateTime(now_et, '%H:%i') AS time_checked_et,
    multiIf(toDayOfWeek(today_et) >= 6, 'weekend',
            today_full_holiday = 1, concat('holiday — ', today_holiday_name),
            today_early = 1, concat('early close (1:00 pm) — ', today_holiday_name),
            'normal weekday') AS todays_calendar,
    multiIf(NOT today_is_session, 'CLOSED',
            min_now < 240, 'CLOSED (overnight)',
            min_now < 570, 'PREMARKET',
            min_now < close_min, 'OPEN',
            min_now < after_hours_end, 'AFTER-HOURS',
            'CLOSED (overnight)') AS market_status,
    toString(if(today_is_session AND min_now < close_min, today_et, next_sess_after_today)) AS next_regular_session,
    dateDiff('day', today_et, if(today_is_session AND min_now < close_min, today_et, next_sess_after_today)) AS days_to_next_session

SELECT toString(date) AS closure_date,
       formatDateTime(date, '%a') AS falls_on,
       any(name) AS holiday,
       any(status) AS day_status,
       any(if(status = 'early-close', formatDateTime(toTimeZone(close, 'America/New_York'), '%H:%i'), '')) AS closes_at_et,
       dateDiff('day', today(), date) AS days_away
FROM global_markets.stocks_market_holidays
WHERE exchange = 'NYSE' AND date >= today()
GROUP BY date
ORDER BY date
LIMIT 16

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

WITH rth AS (
    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 >= today() - 450
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
last_sess AS (
    SELECT max(d) AS last_d
    FROM rth
    WHERE bars >= 120 AND d < toDate(toTimeZone(now(), 'America/New_York'))
),
opt AS (
    SELECT round(count() / 1e3, 0) AS spy_option_prints_thousands
    FROM global_markets.options_trades
    WHERE ticker >= 'O:SPY2' AND ticker < 'O:SPY3'
      AND sip_timestamp >= toDateTime((SELECT last_d FROM last_sess), 'America/New_York')
      AND sip_timestamp < toDateTime((SELECT last_d FROM last_sess) + 1, 'America/New_York')
),
last_early AS (
    SELECT toString(max(d)) AS last_early_close_date,
           argMax(bars, d) AS its_bars
    FROM rth WHERE bars BETWEEN 120 AND 330
)
SELECT toString((SELECT last_d FROM last_sess)) AS most_recent_session,
       formatDateTime((SELECT last_d FROM last_sess), '%W') AS weekday,
       (SELECT bars FROM rth WHERE d = (SELECT last_d FROM last_sess)) AS regular_minute_bars,
       (SELECT spy_option_prints_thousands FROM opt) AS spy_option_prints_thousands,
       (SELECT last_early_close_date FROM last_early) AS last_early_close,
       (SELECT its_bars FROM last_early) AS early_close_bars

WITH events AS (
    SELECT arrayJoin([
        (toDate('2012-10-29'), 'Hurricane Sandy — day 1'),
        (toDate('2012-10-30'), 'Hurricane Sandy — day 2'),
        (toDate('2012-10-31'), 'Reopening after Sandy'),
        (toDate('2018-12-05'), 'Mourning — George H. W. Bush'),
        (toDate('2025-01-09'), 'Mourning — Jimmy Carter')
    ]) AS e
),
tape AS (
    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 >= '2012-10-29 00:00:00' AND window_start < '2012-11-01 12:00:00')
        OR (window_start >= '2018-12-05 00:00:00' AND window_start < '2018-12-06 12:00:00')
        OR (window_start >= '2025-01-09 00:00:00' AND window_start < '2025-01-10 12:00:00'))
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
)
SELECT toString(e.1) AS closure_date,
       formatDateTime(e.1, '%a') AS falls_on,
       e.2 AS name,
       toUInt32(ifNull(t.bars, 0)) AS spy_regular_session_bars
FROM events
LEFT JOIN tape AS t ON t.d = events.e.1
ORDER BY e.1

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           round(argMin(toFloat64(open), window_start), 2) AS open_px,
           round(argMax(toFloat64(close), window_start), 2) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 75
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
paired AS (
    SELECT d,
           open_px,
           lagInFrame(d) OVER (ORDER BY d) AS prev_d,
           lagInFrame(close_px) OVER (ORDER BY d) AS prev_close
    FROM daily
)
SELECT concat(formatDateTime(prev_d, '%a '), toString(prev_d)) AS last_session_before,
       concat(formatDateTime(d, '%a '), toString(d)) AS reopening_session,
       dateDiff('day', prev_d, d) AS days_dark,
       toString(prev_close) AS close_before_break,
       toString(open_px) AS reopening_print,
       round((open_px - prev_close) / prev_close * 100, 2) AS gap_pct
FROM paired
WHERE prev_close > 0 AND dateDiff('day', prev_d, d) >= 3
ORDER BY d

WITH stock_days AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= today() - 370
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
    HAVING count() >= 100
)
SELECT toString(d) AS session_date,
       formatDateTime(d, '%W') AS weekday
FROM stock_days
WHERE d <= (SELECT max(date) FROM global_markets.treasury_yields)
  AND d NOT IN (SELECT date FROM global_markets.treasury_yields WHERE date >= today() - 370)
ORDER BY d