2026年7月9日相場まとめ 数字で見る一日

SELECT
    ticker,
    round(prior_close, 2) AS prior_close,
    formatDateTime(first_pm_bar, '%H:%i') AS first_premkt_bar_et,
    round(premkt_last, 2) AS premkt_last,
    round((premkt_last / prior_close - 1) * 100, 2) AS premkt_pct,
    round(premkt_shares / 1e3, 1) AS premkt_shares_k,
    round((day_open / prior_close - 1) * 100, 2) AS gap_pct,
    round((day_open / premkt_last - 1) * 100, 2) AS open_vs_premkt_pct
FROM (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')) AS prior_close,
        minIf(toTimeZone(window_start, 'America/New_York'), window_start >= '2026-07-09 08:00:00' AND window_start < '2026-07-09 13:30:00') AS first_pm_bar,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 08:00:00' AND window_start < '2026-07-09 13:30:00')) AS premkt_last,
        sumIf(toFloat64(volume), window_start >= '2026-07-09 08:00:00' AND window_start < '2026-07-09 13:30:00') AS premkt_shares,
        toFloat64(argMinIf(open, window_start, window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00')) AS day_open
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'QQQ', 'KLAC', 'LRCX', 'META', 'NVDA')
      AND ((window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')
        OR (window_start >= '2026-07-09 08:00:00' AND window_start < '2026-07-09 20:00:00'))
    GROUP BY ticker
)
ORDER BY ticker

SELECT
    ticker,
    toUInt32(sum(overnight_articles)) AS overnight_articles,
    toUInt32(sum(session_articles)) AS session_articles,
    toUInt32(sum(sec_filings)) AS sec_filings,
    toUInt32(sum(filings_8k)) AS filings_8k
FROM (
    SELECT
        arrayJoin(['AMD', 'KLAC', 'LRCX', 'META', 'MSFT', 'MU', 'NVDA', 'TER', 'WDC']) AS ticker,
        toUInt64(0) AS overnight_articles, toUInt64(0) AS session_articles,
        toUInt64(0) AS sec_filings, toUInt64(0) AS filings_8k
    UNION ALL
    SELECT
        arrayJoin(tickers) AS ticker,
        countIf(published_utc < '2026-07-09 13:30:00') AS overnight_articles,
        countIf(published_utc >= '2026-07-09 13:30:00') AS session_articles,
        toUInt64(0) AS sec_filings, toUInt64(0) AS filings_8k
    FROM global_markets.stocks_news
    WHERE published_utc >= '2026-07-08 20:00:00' AND published_utc < '2026-07-09 20:00:00'
    GROUP BY ticker
    UNION ALL
    SELECT
        ticker, toUInt64(0), toUInt64(0),
        count() AS sec_filings,
        countIf(form_type = '8-K') AS filings_8k
    FROM global_markets.stocks_sec_edgar_index
    WHERE filing_date >= '2026-07-08' AND filing_date <= '2026-07-09'
    GROUP BY ticker
)
WHERE ticker IN ('AMD', 'KLAC', 'LRCX', 'META', 'MSFT', 'MU', 'NVDA', 'TER', 'WDC')
GROUP BY ticker
ORDER BY ticker

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-08 13:30:00' AND window_start < '2026-07-08 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-09 13:30:00' AND window_start < '2026-07-09 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
FROM sess s LEFT JOIN prior p ON s.ticker = p.ticker
ORDER BY ticker

SELECT
    round(anyIf(cc_pct, ticker = 'QQQ' AND d = toDate('2026-07-09')), 2) AS qqq_close_over_close_pct,
    arrayCount(x -> x > abs(anyIf(cc_pct, ticker = 'QQQ' AND d = toDate('2026-07-09'))), groupArrayIf(abs(cc_pct), ticker = 'QQQ' AND d != toDate('2026-07-09'))) + 1 AS qqq_abs_move_rank,
    countIf(ticker = 'QQQ' AND isFinite(cc_pct)) AS qqq_sessions_compared,
    round(anyIf(oc_pct, ticker = 'SPY' AND d = toDate('2026-07-09')), 2) AS spy_open_to_close_pct,
    arrayCount(x -> x > abs(anyIf(oc_pct, ticker = 'SPY' AND d = toDate('2026-07-09'))), groupArrayIf(abs(oc_pct), ticker = 'SPY' AND d != toDate('2026-07-09'))) + 1 AS spy_abs_move_rank,
    countIf(ticker = 'SPY') AS spy_sessions_compared,
    toString(min(d)) AS first_session
FROM (
    SELECT ticker, d,
           if(isFinite(close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY d) - 1),
              (close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY d) - 1) * 100, NULL) AS cc_pct,
           oc_pct
    FROM (
        SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMax(toFloat64(close), window_start) AS close_px,
               (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS oc_pct
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('SPY', 'QQQ')
          AND window_start >= toDateTime('2026-06-08 13:30:00')
          AND window_start < toDateTime('2026-07-10 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY ticker, d
    )
)

SELECT
    countIf(close_9 > close_8 AND close_8 > 0 AND dv_9 >= 1000000) AS advancers,
    countIf(close_9 < close_8 AND close_8 > 0 AND dv_9 >= 1000000) AS decliners,
    countIf(close_9 = close_8 AND close_8 > 0 AND dv_9 >= 1000000) AS unchanged,
    countIf(close_8 > 0 AND close_9 > 0 AND dv_9 >= 1000000) AS liquid_tickers,
    countIf(close_8 > 0 AND close_9 > 0) AS traded_both_sessions,
    countIf(close_8 > 0 AND close_9 > 0) - countIf(close_8 > 0 AND close_9 > 0 AND dv_9 >= 1000000) AS dropped_by_liquidity_filter,
    round(100.0 * countIf(close_9 > close_8 AND close_8 > 0 AND dv_9 >= 1000000) / countIf(close_8 > 0 AND close_9 > 0 AND dv_9 >= 1000000), 1) AS advancer_pct,
    round(100.0 * countIf(close_8 > close_7 AND close_7 > 0 AND dv_8 >= 1000000) / countIf(close_8 > 0 AND close_7 > 0 AND dv_8 >= 1000000), 1) AS jul8_advancer_pct
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-07 20:00:00')) AS close_7,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')) AS close_8,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00')) AS close_9,
           sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00') AS dv_9,
           sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00') AS dv_8
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-09 20:00:00'
    GROUP BY ticker
)

WITH per_day AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(toFloat64(close), window_start) AS c,
           sum(toFloat64(close) * toFloat64(volume)) AS dv
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= '2026-07-02 13:30:00' AND window_start < '2026-07-09 20:00:00'
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    GROUP BY ticker, d
),
lagged AS (
    SELECT ticker, d, c, dv,
           lagInFrame(c) OVER (PARTITION BY ticker ORDER BY d) AS prev_c
    FROM per_day
)
SELECT
    toString(d) AS date,
    round(anyIf((c / prev_c - 1) * 100, ticker = 'SPY'), 2) AS spy_pct,
    round(anyIf((c / prev_c - 1) * 100, ticker = 'QQQ'), 2) AS qqq_pct,
    round(100.0 * countIf(c > prev_c AND prev_c > 0 AND dv >= 1000000) / countIf(c > 0 AND prev_c > 0 AND dv >= 1000000), 1) AS advancer_pct,
    countIf(c > 0 AND prev_c > 0 AND dv >= 1000000) AS liquid_names
FROM lagged
WHERE d >= toDate('2026-07-06')
GROUP BY d
ORDER BY d

SELECT
    sector,
    count() AS names,
    round(avg(pct_chg), 2) AS avg_pct_chg,
    round(min(pct_chg), 2) AS worst_name_pct,
    round(max(pct_chg), 2) AS best_name_pct,
    round(avg(pct_chg) - max(avg(pct_chg)) OVER (), 2) AS gap_to_best_sector_pct
FROM (
    SELECT
        ticker,
        multiIf(ticker IN ('AMD', 'AVGO', 'KLAC'), 'Semiconductors',
                ticker IN ('AAPL', 'MSFT', 'GOOGL'), 'Big tech',
                ticker IN ('JPM', 'BAC', 'GS'), 'Financials',
                ticker IN ('CAT', 'HON', 'GE'), 'Industrials',
                ticker IN ('XOM', 'CVX', 'COP'), 'Energy',
                ticker IN ('JNJ', 'UNH', 'PFE'), 'Healthcare',
                ticker IN ('KO', 'PG', 'WMT'), 'Staples',
                'Utilities') AS sector,
        (toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'))
         / toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')) - 1) * 100 AS pct_chg
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AMD', 'AVGO', 'KLAC', 'AAPL', 'MSFT', 'GOOGL', 'JPM', 'BAC', 'GS', 'CAT', 'HON', 'GE',
                     'XOM', 'CVX', 'COP', 'JNJ', 'UNH', 'PFE', 'KO', 'PG', 'WMT', 'NEE', 'DUK', 'SO')
      AND window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-09 20:00:00'
    GROUP BY ticker
)
GROUP BY sector
ORDER BY avg_pct_chg DESC

WITH per_name AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-09 00:00:00')) AS prior_close,
        toFloat64(argMinIf(open, window_start, window_start >= '2026-07-09 00:00:00')) AS day_open,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 00:00:00')) AS day_close,
        maxIf(toFloat64(high), window_start >= '2026-07-09 00:00:00') AS day_high,
        minIf(toFloat64(low), window_start >= '2026-07-09 00:00:00') AS day_low,
        round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-09 00:00:00') / 1e9, 2) AS day_dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AMD', 'AVGO', 'INTC', 'KLAC', 'LRCX', 'MRVL', 'MU', 'NVDA', 'SNDK', 'SOXL', 'SOXS', 'STX', 'TER', 'WDC')
      AND ((window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')
        OR (window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'))
    GROUP BY ticker
)
SELECT
    ticker,
    round(prior_close, 2) AS prior_close,
    round(day_open, 2) AS day_open,
    round(day_close, 2) AS day_close,
    round((day_open / prior_close - 1) * 100, 2) AS gap_pct,
    round((day_close / day_open - 1) * 100, 2) AS intraday_pct,
    round((day_close / prior_close - 1) * 100, 2) AS pct_chg,
    day_dollar_bn
FROM per_name
ORDER BY ticker

SELECT
    countIf(close_9 > close_8) AS jul9_green,
    countIf(close_9 < close_8) AS jul9_red,
    countIf(close_8 > close_7) AS jul8_green,
    round(anyIf((close_9 / close_8 - 1) * 100, ticker = 'NVDA'), 2) AS nvda_jul9_pct,
    round(anyIf((close_8 / close_7 - 1) * 100, ticker = 'NVDA'), 2) AS nvda_jul8_pct,
    count() AS names_counted
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-07 20:00:00')) AS close_7,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')) AS close_8,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00')) AS close_9
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AMD', 'AVGO', 'INTC', 'KLAC', 'LRCX', 'MRVL', 'MU', 'NVDA', 'SNDK', 'SOXL', 'SOXS', 'STX', 'TER', 'WDC')
      AND window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-09 20:00:00'
    GROUP BY ticker
)

WITH per_name AS (
    SELECT
        ticker,
        toFloat64(argMaxIf(close, window_start, window_start < '2026-07-09 00:00:00')) AS prior_close,
        toFloat64(argMinIf(open, window_start, window_start >= '2026-07-09 00:00:00')) AS day_open,
        toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-09 00:00:00')) AS day_close,
        round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-09 00:00:00') / 1e9, 2) AS day_dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'AMZN', 'CVX', 'GOOGL', 'JNJ', 'META', 'MSFT', 'TSLA')
      AND ((window_start >= '2026-07-08 13:30:00' AND window_start < '2026-07-08 20:00:00')
        OR (window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'))
    GROUP BY ticker
)
SELECT
    ticker,
    round(prior_close, 2) AS prior_close,
    round(day_open, 2) AS day_open,
    round(day_close, 2) AS day_close,
    round((day_open / prior_close - 1) * 100, 2) AS gap_pct,
    round((day_close / day_open - 1) * 100, 2) AS intraday_pct,
    round((day_close / prior_close - 1) * 100, 2) AS pct_chg,
    day_dollar_bn
FROM per_name
ORDER BY ticker

SELECT leaderboard, ticker, dollar_volume_bn, shares_m,
    round(1000 * dollar_volume_bn / shares_m, 2) AS implied_avg_price,
    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-09 13:30:00' AND window_start < '2026-07-09 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-09 13:30:00' AND window_start < '2026-07-09 20:00:00'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    ORDER BY shares_m DESC
    LIMIT 4
)
ORDER BY leaderboard, pct_of_board_leader DESC

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

WITH
    (
        SELECT (groupArray(und), groupArray(strike), groupArray(typ), groupArray(vol), groupArray(avg_px), groupArray(is_0dte))
        FROM (
            SELECT any(underlying_symbol) AS und, any(toFloat64(strike_price)) AS strike, any(option_type) AS typ,
                   sum(size) AS vol, round(avg(toFloat64(price)), 3) AS avg_px,
                   if(substring(ticker, length(ticker) - 14, 6) = '260709', 1, 0) AS is_0dte
            FROM global_markets.options_trades
            WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
            GROUP BY ticker
            ORDER BY vol DESC
            LIMIT 3
        )
    ) AS top3,
    (
        SELECT round(toFloat64(argMax(close, window_start)), 2)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'
    ) AS spy_regular_close,
    (
        SELECT round(toFloat64(sum(size)) / 1e6, 2)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-08 00:00:00' AND sip_timestamp < '2026-07-09 00:00:00'
    ) AS jul8_contracts_m,
    (
        SELECT round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260708') / sum(size), 1)
        FROM global_markets.options_trades
        WHERE sip_timestamp >= '2026-07-08 00:00:00' AND sip_timestamp < '2026-07-09 00:00:00'
    ) AS jul8_pct_0dte
SELECT
    round(count() / 1e6, 2) AS option_prints_m,
    round(toFloat64(sum(size)) / 1e6, 2) AS contracts_m,
    jul8_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) = '260709') / sum(size), 1) AS pct_0dte,
    jul8_pct_0dte,
    spy_regular_close,
    top3.1[1] AS top1_und, top3.2[1] AS top1_strike, top3.3[1] AS top1_type,
    round(toFloat64(top3.4[1]) / 1e3, 1) AS top1_contracts_k, top3.5[1] AS top1_avg_px, top3.6[1] AS top1_is_0dte,
    top3.1[2] AS top2_und, top3.2[2] AS top2_strike, top3.3[2] AS top2_type, top3.6[2] AS top2_is_0dte,
    top3.1[3] AS top3_und, top3.2[3] AS top3_strike, top3.3[3] AS top3_type, top3.6[3] AS top3_is_0dte,
    round(toFloat64(top3.2[1]) - spy_regular_close, 2) AS top1_moneyness
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'

WITH (
    SELECT toFloat64(argMax(close, window_start))
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY' AND window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00'
) AS spy_close
SELECT
    multiIf(dist < -2, 'Strike >2% below close',
            dist < -0.5, 'Strike 0.5-2% below',
            dist <= 0.5, 'Strike within 0.5%',
            dist <= 2, 'Strike 0.5-2% above',
            'Strike >2% above close') AS strike_bucket,
    round(toFloat64(sumIf(size, option_type = 'C')) / 1e3, 1) AS call_contracts_k,
    round(toFloat64(sumIf(size, option_type = 'P')) / 1e3, 1) AS put_contracts_k,
    round(100.0 * sumIf(size, option_type = 'C') / sum(size), 1) AS call_share_pct
FROM (
    SELECT option_type, size, (toFloat64(strike_price) / spy_close - 1) * 100 AS dist
    FROM global_markets.options_trades
    WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
      AND underlying_symbol = 'SPY'
      AND substring(ticker, length(ticker) - 14, 6) = '260709'
)
GROUP BY strike_bucket
ORDER BY min(dist)

SELECT
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09')) / 1e6, 2) AS jul9_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-08')) / 1e6, 2) AS jul8_updates_m,
    round((countIf(toDate(sip_timestamp) = toDate('2026-07-09')) / countIf(toDate(sip_timestamp) = toDate('2026-07-08')) - 1) * 100, 1) AS day_over_day_pct,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09') AND ticker = 'SPY') / 1e6, 2) AS jul9_spy_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09') AND ticker = 'QQQ') / 1e6, 2) AS jul9_qqq_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09') AND ticker = 'NVDA') / 1e6, 2) AS jul9_nvda_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09') AND ticker = 'TSLA') / 1e6, 2) AS jul9_tsla_updates_m,
    round(countIf(toDate(sip_timestamp) = toDate('2026-07-09') AND ticker = 'MU') / 1e6, 2) AS jul9_mu_updates_m
FROM global_markets.cache_stocks_quotes
WHERE sip_timestamp >= '2026-07-08 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'

SELECT
    ticker,
    round(quantileExact(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000), 2) AS median_spread_bps
FROM global_markets.cache_stocks_quotes
WHERE ticker IN ('SPY', 'QQQ', 'NVDA', 'TSLA', 'MU', 'SNDK', 'AVGO')
  AND sip_timestamp >= '2026-07-09 13:30:00' AND sip_timestamp < '2026-07-09 20:00:00'
  AND bid_price > 0 AND ask_price > 0 AND ask_price > bid_price
GROUP BY ticker
ORDER BY median_spread_bps ASC

SELECT
    round(anyIf(avg_spread_cents, d = toDate('2026-07-09')), 3) AS jul9_avg_spread_cents,
    arrayCount(x -> x < anyIf(avg_spread_cents, d = toDate('2026-07-09')), groupArrayIf(avg_spread_cents, d != toDate('2026-07-09'))) + 1 AS tightness_rank,
    count() AS sessions_compared,
    round(min(avg_spread_cents), 3) AS tightest_session_cents,
    round(max(avg_spread_cents), 3) AS widest_session_cents,
    toString(min(d)) AS first_session
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
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2026-06-08 00:00:00')
      AND sip_timestamp < toDateTime('2026-07-10 00:00:00')
      AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
    GROUP BY d
)

WITH
    (SELECT count() FROM global_markets.cache_options_quotes WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00') AS jul9_options_rows,
    (SELECT count() FROM global_markets.cache_stocks_quotes WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00') AS jul9_stock_quote_rows
SELECT
    round(jul9_options_rows / 1e9, 2) AS jul9_options_bn,
    round(jul9_options_rows / jul9_stock_quote_rows, 1) AS options_to_stock_ratio,
    round((SELECT count() FROM global_markets.cache_options_quotes WHERE ticker >= 'O:SPY26' AND ticker < 'O:SPY27' AND sip_timestamp >= '2026-07-09 13:30:00' AND sip_timestamp < '2026-07-09 20:00:00') / 1e6, 0) AS jul9_spy_options_m

SELECT
    (SELECT count() FROM global_markets.treasury_yields WHERE date = '2026-07-09') AS jul9_print_rows,
    toString(any(date)) AS latest_print_date,
    round(toFloat64(any(yield_2_year)), 2) AS latest_2y_pct,
    round(toFloat64(any(yield_10_year)), 2) AS latest_10y_pct,
    round(toFloat64(any(yield_30_year)), 2) AS latest_30y_pct,
    round(toFloat64(any(yield_10_year)) - toFloat64(any(yield_2_year)), 2) AS latest_2s10s_pct
FROM global_markets.treasury_yields
WHERE date = '2026-07-09'

WITH
    (
        SELECT (count(), uniqExact(publisher))
        FROM global_markets.stocks_news
        WHERE toDate(toTimeZone(published_utc, 'America/New_York')) = '2026-07-09'
    ) AS news,
    (
        SELECT (argMax(t, (n, t)), max(n), max(n) - arraySort(x -> -x, groupArray(n))[2])
        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-09'
            )
            WHERE t != 'SPCX'
            GROUP BY t
        )
    ) AS top_news
SELECT
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-07-09') AS ex_dividend_records,
    (SELECT count() FROM global_markets.stocks_splits WHERE execution_date = '2026-07-09') AS splits_executed,
    (SELECT countIf(toFloat64(split_from) > toFloat64(split_to)) FROM global_markets.stocks_splits WHERE execution_date = '2026-07-09') AS reverse_splits,
    (SELECT countIf(toFloat64(split_to) > toFloat64(split_from)) FROM global_markets.stocks_splits WHERE execution_date = '2026-07-09') AS forward_splits,
    (SELECT countIf(form_type = '424B2') FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-09') AS fil_424b2,
    (SELECT countIf(form_type = '4') FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-09') AS fil_form4,
    (SELECT countIf(form_type = '8-K') FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-09') AS fil_8k,
    (SELECT count() FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-09') AS fil_total,
    news.1 AS news_articles, news.2 AS news_publishers,
    top_news.1 AS top_news_ticker, top_news.2 AS top_news_n, top_news.3 AS top_news_lead_over_next,
    if(top_news.1 = 'NVDA', 1, 0) AS top_news_is_nvda

SELECT
    toString(min(d)) AS next_session_date,
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date = '2026-07-10') AS next_session_holiday_rows,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-07-10') AS ex_div_records_next,
    (SELECT countIf(ticker IN ('AAPL', 'MSFT', 'JNJ', 'KO', 'PG', 'XOM', 'CVX', 'JPM', 'WMT', 'PEP', 'HON', 'CAT'))
     FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-07-10') AS household_ex_div_next,
    (SELECT round(toFloat64(sum(size)) / 1e6, 2) FROM global_markets.options_trades
     WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
       AND underlying_symbol = 'SPY' AND substring(ticker, length(ticker) - 14, 6) = '260710') AS spy_next_expiry_contracts_m,
    (SELECT round(toFloat64(sum(size)) / 1e6, 2) FROM global_markets.options_trades
     WHERE sip_timestamp >= '2026-07-09 00:00:00' AND sip_timestamp < '2026-07-10 00:00:00'
       AND underlying_symbol = 'SPY' AND substring(ticker, length(ticker) - 14, 6) = '260717') AS spy_monthly_expiry_contracts_m,
    (SELECT toString(max(settlement_date)) FROM global_markets.stocks_short_interest
     WHERE _ingest_time < '2026-07-11 00:00:00') AS latest_short_settlement,
    (SELECT count() FROM global_markets.stocks_short_interest
     WHERE settlement_date = '2026-07-15' AND _ingest_time < '2026-07-11 00:00:00') AS jul15_short_rows
FROM (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY' AND window_start >= '2026-07-10 00:00:00' AND window_start < '2026-07-14 00:00:00'
)

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-09 13:30:00' AND window_start < '2026-07-09 20:00:00') AS regular_session_bars,
    uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), window_start >= '2026-07-09 13:30:00' AND window_start < '2026-07-09 20:00:00') AS day_sessions,
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date = '2026-07-09') AS jul9_holiday_rows,
    (SELECT toString(min(date)) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-09' AND status = 'closed') AS next_closure_date,
    (SELECT concat(monthName(min(date)), ' ', toString(toDayOfMonth(min(date))), ', ', toString(toYear(min(date))))
     FROM global_markets.stocks_market_holidays WHERE date > '2026-07-09' AND status = 'closed') AS next_closure_label,
    (SELECT argMin(name, date) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-09' AND status = 'closed') AS next_closure_name
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY' AND window_start >= '2026-07-09 00:00:00' AND window_start < '2026-07-10 00:00:00'