The most recent session on the tape: bar count, same-day SPY options prints, and the last half-day observed

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