মার্কেট Recap: 6 July 2026 এর সংখ্যাসমূহ

WITH prior AS (
    SELECT ticker, argMax(close, window_start) AS prior_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
      AND window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00'
    GROUP BY ticker
),
sess AS (
    SELECT ticker,
           argMin(open, window_start) AS day_open,
           argMax(close, window_start) AS day_close,
           max(high) AS day_high,
           min(low) AS day_low,
           round(toFloat64(sum(volume)) / 1e6, 1) AS shares_traded_m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
      AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
    GROUP BY ticker
)
SELECT
    s.ticker AS ticker,
    round(toFloat64(p.prior_close), 2) AS prior_close,
    round(toFloat64(s.day_open), 2) AS day_open,
    round(toFloat64(s.day_close), 2) AS day_close,
    round((toFloat64(s.day_open) / toFloat64(p.prior_close) - 1) * 100, 2) AS gap_pct,
    round((toFloat64(s.day_close) / toFloat64(s.day_open) - 1) * 100, 2) AS intraday_pct,
    round((toFloat64(s.day_close) / toFloat64(p.prior_close) - 1) * 100, 2) AS pct_change,
    round(toFloat64(s.day_high), 2) AS day_high,
    round(toFloat64(s.day_low), 2) AS day_low,
    s.shares_traded_m AS shares_traded_m
FROM sess s
JOIN prior p ON s.ticker = p.ticker
ORDER BY s.ticker

SELECT round(anyIf(oc_pct, d = toDate('2026-07-06')), 2) AS day_move_pct,
       arrayCount(x -> x > abs(anyIf(oc_pct, d = toDate('2026-07-06'))), groupArrayIf(abs(oc_pct), d != toDate('2026-07-06'))) + 1 AS abs_move_rank,
       count() AS sessions_compared,
       toString(min(d)) AS first_session
FROM (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS oc_pct
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-06-06 00:00:00')
      AND window_start < toDateTime('2026-07-07 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY d
)

WITH per_ticker AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 00:00:00')) AS prior_close,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-06 00:00:00')) AS day_close,
        sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 00:00:00') AS day_dollar_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE (window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00')
       OR (window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00')
    GROUP BY ticker
)
SELECT
    countIf(day_close > prior_close AND day_dollar_volume >= 1000000) AS advancers,
    countIf(day_close < prior_close AND day_dollar_volume >= 1000000) AS decliners,
    countIf(day_close = prior_close AND day_dollar_volume >= 1000000) AS unchanged,
    countIf(day_dollar_volume >= 1000000) AS liquid_tickers,
    count() AS tickers_traded_both_sessions,
    count() - countIf(day_dollar_volume >= 1000000) AS dropped_by_liquidity_filter,
    round(100.0 * countIf(day_close > prior_close AND day_dollar_volume >= 1000000)
        / countIf(day_dollar_volume >= 1000000), 1) AS advancer_pct
FROM per_ticker
WHERE prior_close > 0 AND day_close > 0

WITH per_name AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 00:00:00')) AS prior_close,
        toFloat64(argMinIf(open, window_start, window_start >= '2026-07-06 00:00:00')) AS day_open,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-06 00:00:00')) AS day_close,
        round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 00:00:00') / 1e9, 2) AS day_dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('XLB', 'XLC', 'XLE', 'XLF', 'XLI', 'XLK', 'XLP', 'XLRE', 'XLU', 'XLV', 'XLY')
      AND ((window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00')
        OR (window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'))
    GROUP BY ticker
)
SELECT
    ticker,
    round((day_close / prior_close - 1) * 100, 2) AS pct_chg,
    round((day_open / prior_close - 1) * 100, 2) AS gap_pct,
    round((day_close / day_open - 1) * 100, 2) AS intraday_pct,
    day_dollar_bn,
    round(max((day_close / prior_close - 1) * 100) OVER () - (day_close / prior_close - 1) * 100, 2) AS points_behind_leader
FROM per_name
ORDER BY pct_chg DESC, ticker ASC

WITH per_name AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 00:00:00')) AS prior_close,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-06 00:00:00')) AS day_close,
        maxIf(toFloat64(high), window_start >= '2026-07-06 00:00:00') AS day_high,
        minIf(toFloat64(low), window_start >= '2026-07-06 00:00:00') AS day_low,
        argMinIf(window_start, toFloat64(low), window_start >= '2026-07-06 00:00:00') AS low_bar,
        argMaxIf(window_start, toFloat64(high), window_start >= '2026-07-06 00:00:00') AS high_bar,
        round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 00:00:00') / 1e9, 2) AS day_dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('MU', 'SNDK', 'STX', 'WDC')
      AND ((window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00')
        OR (window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'))
    GROUP BY ticker
)
SELECT
    ticker,
    round(prior_close, 2) AS prior_close,
    round(day_close, 2) AS day_close,
    round((day_close / prior_close - 1) * 100, 2) AS pct_chg,
    round(day_high, 2) AS day_high,
    formatDateTime(toTimeZone(high_bar, 'America/New_York'), '%H:%i') AS day_high_et,
    round(day_low, 2) AS day_low,
    formatDateTime(toTimeZone(low_bar, 'America/New_York'), '%H:%i') AS day_low_et,
    round((day_high / day_low - 1) * 100, 2) AS range_pct,
    day_dollar_bn
FROM per_name
ORDER BY ticker

WITH per_name AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-06 00:00:00')) AS prior_close,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-06 00:00:00')) AS day_close,
        maxIf(toFloat64(high), window_start >= '2026-07-06 00:00:00') AS day_high,
        minIf(toFloat64(low), window_start >= '2026-07-06 00:00:00') AS day_low,
        argMinIf(window_start, toFloat64(low), window_start >= '2026-07-06 00:00:00') AS low_bar,
        argMaxIf(window_start, toFloat64(high), window_start >= '2026-07-06 00:00:00') AS high_bar,
        round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 00:00:00') / 1e9, 2) AS day_dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'TSLA')
      AND ((window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00')
        OR (window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'))
    GROUP BY ticker
)
SELECT
    ticker,
    round(prior_close, 2) AS prior_close,
    round(day_close, 2) AS day_close,
    round((day_close / prior_close - 1) * 100, 2) AS pct_chg,
    round(day_high, 2) AS day_high,
    formatDateTime(toTimeZone(high_bar, 'America/New_York'), '%H:%i') AS day_high_et,
    round(day_low, 2) AS day_low,
    formatDateTime(toTimeZone(low_bar, 'America/New_York'), '%H:%i') AS day_low_et,
    round((day_high / day_low - 1) * 100, 2) AS range_pct,
    day_dollar_bn
FROM per_name
ORDER BY ticker

SELECT leaderboard, ticker, dollar_volume_bn, shares_m,
    round(100 * if(leaderboard = 'by dollars traded', dollar_volume_bn, shares_m)
        / max(if(leaderboard = 'by dollars traded', dollar_volume_bn, shares_m)) OVER (PARTITION BY leaderboard), 1) AS pct_of_board_leader
FROM (
    SELECT
        'by dollars traded' AS leaderboard,
        ticker,
        round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 2) AS dollar_volume_bn,
        round(sum(toFloat64(volume)) / 1e6, 1) AS shares_m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    ORDER BY dollar_volume_bn DESC
    LIMIT 6
    UNION ALL
    SELECT
        'by shares traded' AS leaderboard,
        ticker,
        round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 2) AS dollar_volume_bn,
        round(sum(toFloat64(volume)) / 1e6, 1) AS shares_m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    ORDER BY shares_m DESC
    LIMIT 4
)
ORDER BY leaderboard ASC, if(leaderboard = 'by dollars traded', dollar_volume_bn, shares_m) DESC

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
    round(sum(toFloat64(volume)) / 1e9, 2) AS shares_bn,
    round(100 * sum(toFloat64(volume)) / max(sum(toFloat64(volume))) OVER (), 1) AS pct_of_biggest_bucket
FROM global_markets.delayed_stocks_minute_aggs
WHERE window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
GROUP BY et_time
ORDER BY et_time

WITH
    (
        SELECT (any(underlying_symbol), any(toFloat64(strike_price)), any(option_type),
                any(toDateOrNull(concat('20', substring(ticker, length(ticker) - 14, 6)))),
                sum(size), count(), round(avg(toFloat64(price)), 3))
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
        GROUP BY ticker
        ORDER BY sum(size) DESC
        LIMIT 1
    ) AS top_contract,
    (
        SELECT round(toFloat64(argMax(close, window_start)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
    ) AS spy_regular_close
SELECT
    round(count() / 1e6, 2) AS option_prints_m,
    round(toFloat64(sum(size)) / 1e6, 2) AS contracts_m,
    round(100.0 * sumIf(size, option_type = 'C') / sum(size), 1) AS call_pct_of_volume,
    round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260706') / sum(size), 1) AS same_day_expiry_pct,
    round(toFloat64(sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260706')) / 1e6, 2) AS same_day_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'SPY')) / 1e6, 2) AS spy_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'QQQ')) / 1e6, 2) AS qqq_contracts_m,
    round(toFloat64(sumIf(size, underlying_symbol = 'TSLA')) / 1e6, 2) AS tsla_contracts_m,
    top_contract.1 AS top_contract_underlying,
    top_contract.2 AS top_contract_strike,
    top_contract.3 AS top_contract_type,
    if(top_contract.3 = 'C', 1, 0) AS top_contract_is_call,
    top_contract.4 AS top_contract_expiry,
    round(toFloat64(top_contract.5) / 1e6, 2) AS top_contract_volume_m,
    round(top_contract.7, 3) AS top_contract_avg_price,
    round(spy_regular_close - top_contract.2, 2) AS spy_close_minus_strike
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'

SELECT
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-06')) / 1e6, 2) AS jul6_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-02')) / 1e6, 2) AS jul2_updates_m,
    round((countIf(toDate(sip_timestamp) = toDate('2026-07-06')) / countIf(toDate(sip_timestamp) = toDate('2026-07-02')) - 1) * 100, 1) AS day_over_day_pct,
    round(countIf(sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 14:00:00') / 1e6, 2) AS jul6_open_half_hour_m,
    round(countIf(sip_timestamp >= '2026-07-06 17:30:00' AND sip_timestamp < '2026-07-06 18:00:00') / 1e6, 2) AS jul6_midday_half_hour_m,
    round(countIf(sip_timestamp >= '2026-07-06 19:30:00' AND sip_timestamp < '2026-07-06 20:00:00') / 1e6, 2) AS jul6_close_half_hour_m
FROM global_markets.cache_stocks_quotes
WHERE (sip_timestamp >= '2026-07-02 00:00:00' AND sip_timestamp < '2026-07-03 00:00:00')
   OR (sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00')

SELECT
    ticker,
    round(quantileExactIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price >= bid_price AND sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 13:42:00'), 2) AS open_spread_bps,
    round(quantileExactIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price >= bid_price AND sip_timestamp >= '2026-07-06 17:30:00' AND sip_timestamp < '2026-07-06 17:42:00'), 2) AS midday_spread_bps,
    round(quantileExactIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price >= bid_price AND sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 13:42:00')
        - quantileExactIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, bid_price > 0 AND ask_price >= bid_price AND sip_timestamp >= '2026-07-06 17:30:00' AND sip_timestamp < '2026-07-06 17:42:00'), 2) AS spread_delta_bps,
    round(countIf(sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 13:42:00') / 1e3, 1) AS open_updates_k,
    round(countIf(sip_timestamp >= '2026-07-06 17:30:00' AND sip_timestamp < '2026-07-06 17:42:00') / 1e3, 1) AS midday_updates_k,
    round(countIf(sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 13:42:00') / countIf(sip_timestamp >= '2026-07-06 17:30:00' AND sip_timestamp < '2026-07-06 17:42:00'), 1) AS quote_rate_ratio,
    round(count() / 1e6, 2) AS session_updates_m,
    countIf(bid_price <= 0 OR ask_price <= 0 OR bid_price > ask_price) AS dropped_invalid_quotes
FROM global_markets.cache_stocks_quotes
WHERE ticker IN ('DIA', 'IWM', 'MSFT', 'MU', 'QQQ', 'SNDK', 'SPY', 'STX', 'TSLA', 'WDC')
  AND sip_timestamp >= '2026-07-06 13:30:00' AND sip_timestamp < '2026-07-06 20:00:00'
GROUP BY ticker
ORDER BY ticker

SELECT
    round(anyIf(avg_spread_cents, d = toDate('2026-07-06')), 3) AS jul6_avg_spread_cents,
    arrayCount(x -> x < anyIf(avg_spread_cents, d = toDate('2026-07-06')), groupArrayIf(avg_spread_cents, d != toDate('2026-07-06'))) + 1 AS tightness_rank,
    count() AS sessions_compared,
    round(max(avg_spread_cents), 3) AS widest_session_cents,
    round(avgIf(avg_spread_cents, d != toDate('2026-07-06')), 3) AS other_sessions_avg_cents,
    replaceAll(formatDateTime(min(d), '%M %e, %Y'), '  ', ' ') AS first_session,
    sum(dropped_invalid) AS dropped_invalid_quotes
FROM (
    SELECT toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
           avgIf(toFloat64(ask_price - bid_price), bid_price > 0 AND ask_price >= bid_price) * 100 AS avg_spread_cents,
           countIf(NOT (bid_price > 0 AND ask_price >= bid_price)) AS dropped_invalid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2026-06-04 00:00:00')
      AND sip_timestamp < toDateTime('2026-07-07 00:00:00')
      AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
    GROUP BY d
)

SELECT
    t.1 AS curve_point,
    round(t.2, 2) AS jul6_yield_pct,
    round((t.2 - t.3) * 100) AS session_change_bp
FROM (
    SELECT arrayJoin([
        ('1 month',  toFloat64(d.yield_1_month),  toFloat64(p.yield_1_month)),
        ('3 month',  toFloat64(d.yield_3_month),  toFloat64(p.yield_3_month)),
        ('1 year',   toFloat64(d.yield_1_year),   toFloat64(p.yield_1_year)),
        ('2 year',   toFloat64(d.yield_2_year),   toFloat64(p.yield_2_year)),
        ('5 year',   toFloat64(d.yield_5_year),   toFloat64(p.yield_5_year)),
        ('10 year',  toFloat64(d.yield_10_year),  toFloat64(p.yield_10_year)),
        ('30 year',  toFloat64(d.yield_30_year),  toFloat64(p.yield_30_year)),
        ('2s10s spread', toFloat64(d.yield_10_year - d.yield_2_year), toFloat64(p.yield_10_year - p.yield_2_year))
    ]) AS t
    FROM (SELECT * FROM global_markets.treasury_yields WHERE date = '2026-07-06') AS d,
         (SELECT * FROM global_markets.treasury_yields WHERE date = '2026-07-02') AS p
)

WITH
    (
        SELECT (count(), uniqExact(publisher))
        FROM global_markets.stocks_news
        WHERE toDate(toTimeZone(published_utc, 'America/New_York')) = '2026-07-06'
    ) AS news,
    (
        SELECT (argMax(t, n), max(n))
        FROM (
            SELECT t, count() AS n
            FROM (
                SELECT arrayJoin(tickers) AS t
                FROM global_markets.stocks_news
                WHERE toDate(toTimeZone(published_utc, 'America/New_York')) = '2026-07-06'
            )
            WHERE t != 'SPCX'
            GROUP BY t
        )
    ) AS top_news
SELECT
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-07-06') AS ex_dividend_records,
    (SELECT count() FROM global_markets.stocks_splits WHERE execution_date = '2026-07-06') AS splits_executed,
    (SELECT countIf(toFloat64(split_from) > toFloat64(split_to)) FROM global_markets.stocks_splits WHERE execution_date = '2026-07-06') AS reverse_splits,
    (SELECT countIf(toFloat64(split_to) > toFloat64(split_from)) FROM global_markets.stocks_splits WHERE execution_date = '2026-07-06') AS forward_splits,
    (SELECT count() FROM global_markets.stocks_ipos WHERE listing_date = '2026-07-06') AS ipos_listed,
    (SELECT uniqExact(accession_number) FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-06') AS sec_filings,
    (SELECT uniqExactIf(accession_number, form_type = '4') FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-06') AS insider_form4_filings,
    (SELECT uniqExactIf(accession_number, form_type = '8-K') FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-06') AS filings_8k,
    news.1 AS news_articles,
    news.2 AS news_publishers,
    top_news.1 AS most_covered_ticker,
    top_news.2 AS most_covered_articles

SELECT
    s.ticker AS ticker,
    toFloat64(s.split_from) AS old_shares,
    toFloat64(s.split_to) AS new_shares,
    round(toFloat64(s.split_from) / toFloat64(s.split_to), 0) AS old_shares_per_new,
    p.prev_close AS jul2_close,
    d.day_close AS jul6_close,
    round((d.day_close / p.prev_close - 1) * 100, 1) AS unadjusted_pct_chg
FROM (
    SELECT ticker, any(split_from) AS split_from, any(split_to) AS split_to
    FROM global_markets.stocks_splits
    WHERE execution_date = '2026-07-06'
    GROUP BY ticker
) s
INNER JOIN (
    SELECT ticker, round(toFloat64(argMax(close, window_start)), 2) AS prev_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-02 20:00:00'
      AND ticker IN (SELECT ticker FROM global_markets.stocks_splits WHERE execution_date = '2026-07-06')
    GROUP BY ticker
    HAVING prev_close > 0
) p ON s.ticker = p.ticker
INNER JOIN (
    SELECT ticker, round(toFloat64(argMax(close, window_start)), 2) AS day_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00'
      AND ticker IN (SELECT ticker FROM global_markets.stocks_splits WHERE execution_date = '2026-07-06')
    GROUP BY ticker
    HAVING day_close > 0
) d ON s.ticker = d.ticker
ORDER BY unadjusted_pct_chg DESC, ticker ASC

SELECT
    formatDateTime(min(toTimeZone(window_start, 'America/New_York')), '%H:%i') AS first_spy_bar_et,
    formatDateTime(max(toTimeZone(window_start, 'America/New_York')), '%H:%i') AS last_spy_bar_et,
    count() AS spy_minute_bars,
    countIf(window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00') AS regular_session_bars,
    uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00') AS day_sessions,
    (SELECT count() FROM global_markets.delayed_stocks_minute_aggs
     WHERE ticker = 'SPY' AND window_start >= '2026-07-03 00:00:00' AND window_start < '2026-07-04 00:00:00') AS jul3_spy_bars,
    (SELECT replaceAll(formatDateTime(min(date), '%M %e, %Y'), '  ', ' ') FROM global_markets.stocks_market_holidays WHERE date > '2026-07-06' AND status = 'closed') AS next_closure_date,
    (SELECT argMin(name, date) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-06' AND status = 'closed') AS next_closure_name
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY' AND window_start >= '2026-07-06 00:00:00' AND window_start < '2026-07-07 00:00:00'