NVDA: Sit-Out, Then Surge, Week of July 6

WITH
    (
        SELECT (toString(argMax(et_date, c)), max(c), argMax(c, et_date))
        FROM (
            SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
                   argMax(toFloat64(close), window_start) AS c
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'NVDA'
              AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
              AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
            GROUP BY et_date
        )
    ) AS closes,
    (
        SELECT max(toFloat64(high)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    ) AS hi,
    (
        SELECT min(toFloat64(low)) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    ) AS lo,
    (
        SELECT argMax(toFloat64(close), window_start) FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-03 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS prior_week_close
SELECT
    round(prior_week_close, 2) AS prior_friday_close,
    round(toFloat64(argMinIf(open, window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)), 2) AS week_open,
    closes.3 AS week_close,
    round((closes.3 / prior_week_close - 1) * 100, 1) AS week_change_pct,
    closes.1 AS peak_close_date,
    round(closes.2, 2) AS peak_close,
    round(hi, 2) AS week_high,
    formatDateTime(toTimeZone(minIf(window_start, toFloat64(high) >= hi - 0.011), 'America/New_York'), '%Y-%m-%d %H:%i') AS week_high_first_bar_et,
    round(lo, 2) AS week_low,
    formatDateTime(toTimeZone(minIf(window_start, toFloat64(low) <= lo + 0.011), 'America/New_York'), '%Y-%m-%d %H:%i') AS week_low_bar_et,
    round(toFloat64(sum(volume)) / 1e6, 1) AS week_shares_m,
    round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 1) AS week_dollar_bn,
    round(sumIf(toFloat64(close) * toFloat64(volume), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / 1e9, 1) AS rth_dollar_bn,
    uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS session_days_observed
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'NVDA'
  AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')

SELECT
    et_date,
    close_usd,
    round(if(prev_close = 0, NULL, (close_usd / prev_close - 1) * 100), 1) AS change_pct,
    shares_m,
    dollar_bn
FROM (
    SELECT et_date, close_usd, shares_m, dollar_bn,
           lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
            round(argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS close_usd,
            round(toFloat64(sum(volume)) / 1e6, 1) AS shares_m,
            round(sum(toFloat64(close) * toFloat64(volume)) / 1e9, 2) AS dollar_bn
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA'
          AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
        GROUP BY et_date
    )
)
WHERE et_date >= toDate('2026-07-06')
ORDER BY et_date

SELECT
    toString(d) AS et_date,
    round(maxIf(pct, ticker = 'NVDA'), 1) AS nvda_pct,
    round(maxIf(pct, ticker = 'AMD'), 1) AS amd_pct,
    round(maxIf(pct, ticker = 'MU'), 1) AS mu_pct,
    round(maxIf(pct, ticker = 'INTC'), 1) AS intc_pct,
    round(maxIf(pct, ticker = 'TSM'), 1) AS tsm_pct
FROM (
    SELECT ticker, d,
           (c / lagInFrame(c) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1) * 100 AS pct
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('NVDA', 'AMD', 'MU', 'INTC', 'TSM')
          AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
        GROUP BY ticker, d
    )
)
WHERE d >= toDate('2026-07-06')
GROUP BY d
ORDER BY d

SELECT ticker, round(sum(toFloat64(volume) * toFloat64(close)) / 1e9, 1) AS rth_dollar_bn
FROM global_markets.delayed_stocks_minute_aggs
WHERE window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
  AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
  AND ticker NOT IN ('SPCX')
GROUP BY ticker
ORDER BY rth_dollar_bn DESC
LIMIT 6

WITH (
    SELECT sum(toFloat64(volume) * toFloat64(close))
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
) AS nvda_d,
(
    SELECT sum(toFloat64(volume) * toFloat64(close))
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= toDateTime('2026-07-10 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
) AS nvda_friday,
(
    SELECT (countIf(d > nvda_friday) + 1, max(d))
    FROM (
        SELECT ticker, sum(toFloat64(volume) * toFloat64(close)) AS d
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= toDateTime('2026-07-10 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
          AND ticker NOT IN ('SPCX', 'NVDA')
        GROUP BY ticker
    )
) AS friday_field
SELECT
    countIf(d > nvda_d AND ticker != 'NVDA') + 1 AS nvda_rank,
    round(nvda_d / 1e9, 1) AS nvda_dollar_bn,
    countIf(d > nvda_d AND ticker NOT IN ('SPY', 'QQQ', 'NVDA')) AS single_stocks_above_nvda,
    round(100 * nvda_d / max(d), 1) AS pct_of_leader,
    friday_field.1 AS friday_rank,
    round(nvda_friday / 1e9, 2) AS friday_nvda_rth_bn,
    round((nvda_friday - friday_field.2) / 1e9, 2) AS friday_margin_over_next_bn
FROM (
    SELECT ticker, sum(toFloat64(volume) * toFloat64(close)) AS d
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
)

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS c,
        maxIf(toFloat64(high), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS h,
        minIf(toFloat64(low), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) AS l
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= toDateTime('2025-07-11 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    GROUP BY et_date
    HAVING countIf((toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) > 0
)
SELECT
    round(argMax(c, et_date), 2) AS friday_close,
    round(max(h), 2) AS high_52w,
    argMax(toString(et_date), (h, et_date)) AS high_52w_date,
    round(100 * (1 - argMax(c, et_date) / max(h)), 1) AS pct_below_52w_high,
    round(min(l), 2) AS low_52w,
    argMax(toString(et_date), (-l, et_date)) AS low_52w_date,
    round(100 * (argMax(c, et_date) / min(l) - 1), 1) AS close_vs_52w_low_pct,
    round(arrayAvg(arrayMap(t -> t.2, arraySlice(arrayReverseSort(t -> t.1, groupArray((et_date, c))), 1, 50))), 2) AS ma_50d,
    round(100 * (argMax(c, et_date) / arrayAvg(arrayMap(t -> t.2, arraySlice(arrayReverseSort(t -> t.1, groupArray((et_date, c))), 1, 50))) - 1), 1) AS close_vs_ma50_pct,
    round(arrayAvg(arrayMap(t -> t.2, arraySlice(arrayReverseSort(t -> t.1, groupArray((et_date, c))), 1, 200))), 2) AS ma_200d,
    round(100 * (argMax(c, et_date) / arrayAvg(arrayMap(t -> t.2, arraySlice(arrayReverseSort(t -> t.1, groupArray((et_date, c))), 1, 200))) - 1), 1) AS close_vs_ma200_pct,
    count() AS trading_days_observed
FROM daily

SELECT
    toString(wk) AS period_start,
    round(ret, 1) AS week_return_pct,
    round(dollar_bn, 1) AS week_rth_dollar_bn
FROM (
    SELECT toStartOfWeek(toDate(toTimeZone(window_start, 'America/New_York')), 1) AS wk,
           uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
           (argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / argMinIf(toFloat64(open), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - 1) * 100 AS ret,
           sumIf(toFloat64(close) * toFloat64(volume), (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / 1e9 AS dollar_bn
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= toDateTime('2026-01-05 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
    GROUP BY wk
    HAVING sessions >= 3
)
ORDER BY period_start

WITH
    (
        SELECT (round(count() / 1e6, 2),
                round(100.0 * countIf(bid_price > 0 AND ask_price > 0 AND ask_price > bid_price) / count(), 2))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'NVDA'
          AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
    ) AS quote_census
SELECT
    round(count() / 1e6, 2) AS prints_m,
    quantileDeterministic(0.5)(toFloat64(size), toUInt64(abs(sequence_number))) AS median_print_shares,
    round(100.0 * countIf(size < 100) / count(), 1) AS odd_lot_pct_of_prints,
    quote_census.1 AS nbbo_updates_m,
    quote_census.2 AS clean_two_sided_pct
FROM global_markets.stocks_trades
WHERE ticker = 'NVDA'
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)

SELECT
    session,
    round(quantileDeterministicIf(0.5)((toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000, toUInt64(toUnixTimestamp64Micro(sip_timestamp)), bid_price > 0 AND ask_price >= bid_price), 2) AS med_spread_bps,
    count() AS quote_updates
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'NVDA'
  AND sip_timestamp >= toDateTime64('2026-07-06 13:30:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
  AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
GROUP BY toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session
ORDER BY session

WITH
    (
        SELECT (toString(d), n)
        FROM (
            SELECT toDate(toTimeZone(published_utc, 'America/New_York')) AS d, count() AS n
            FROM global_markets.stocks_news
            WHERE has(tickers, 'NVDA')
              AND published_utc >= toDateTime('2026-07-06 00:00:00')
              AND published_utc < toDateTime('2026-07-11 04:00:00')
            GROUP BY d ORDER BY n DESC, d ASC LIMIT 1
        )
    ) AS peak_day,
    (
        SELECT (JSONExtractString(publisher, 'name') AS p, count() AS n)
        FROM global_markets.stocks_news
        WHERE has(tickers, 'NVDA')
          AND published_utc >= toDateTime('2026-07-06 00:00:00')
          AND published_utc < toDateTime('2026-07-11 04:00:00')
        GROUP BY p ORDER BY n DESC, p ASC LIMIT 1
    ) AS top_pub
SELECT
    count() AS week_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    peak_day.1 AS peak_day_date,
    peak_day.2 AS peak_day_articles,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-07-09')) AS thursday_articles,
    peak_day.2 - countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-07-09')) AS peak_minus_thursday,
    top_pub.1 AS top_publisher,
    round(100.0 * top_pub.2 / count(), 1) AS top_publisher_share_pct,
    countIf(has(tickers, 'MU')) AS mu_co_articles,
    countIf(has(tickers, 'AMD')) AS amd_co_articles,
    countIf(positionCaseInsensitive(title, 'artificial intelligence') > 0 OR position(title, 'AI') > 0) AS ai_titled_articles
FROM global_markets.stocks_news
WHERE has(tickers, 'NVDA')
  AND published_utc >= toDateTime('2026-07-06 00:00:00')
  AND published_utc < toDateTime('2026-07-11 04:00:00')

WITH
    (
        SELECT ticker
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
        GROUP BY ticker ORDER BY sum(size) DESC, ticker ASC LIMIT 1
    ) AS busiest_t,
    (
        SELECT round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2)
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
          AND sip_timestamp >= toDateTime64('2026-06-08 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-03 00:00:00', 9)
    ) AS trailing_pc,
    (
        SELECT argMax(toFloat64(close), window_start)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-10 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS friday_close,
    (
        SELECT argMax(toFloat64(close), window_start)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA' AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-03 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
    ) AS start_close,
    (
        SELECT concat(substring(e, 3, 2), '-', substring(e, 5, 2))
        FROM (
            SELECT substring(ticker, 7, 6) AS e, sum(size) AS v
            FROM global_markets.options_trades
            WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
              AND substring(ticker, 7, 6) > '260710'
              AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)
            GROUP BY e ORDER BY v DESC, e ASC LIMIT 1
        )
    ) AS back_expiry
SELECT
    round(sum(size) / 1e6, 2) AS contracts_traded_m,
    round(count() / 1e6, 2) AS prints_m,
    round(sum(toFloat64(price) * size) * 100 / 1e9, 2) AS premium_notional_busd,
    round(toFloat64(sumIf(size, substring(ticker, 13, 1) = 'P')) / toFloat64(sumIf(size, substring(ticker, 13, 1) = 'C')), 2) AS week_put_call_ratio,
    trailing_pc AS trailing_4w_put_call_ratio,
    uniqExact(substring(ticker, 7, 6)) AS expiries_traded,
    concat('$', toString(round(toFloat64(toUInt32OrZero(substring(busiest_t, 14, 8))) / 1000, 2)),
           if(substring(busiest_t, 13, 1) = 'P', ' put', ' call'),
           ', expiry 20', substring(busiest_t, 7, 2), '-', substring(busiest_t, 9, 2), '-', substring(busiest_t, 11, 2)) AS busiest_contract,
    round(friday_close - toFloat64(toUInt32OrZero(substring(busiest_t, 14, 8))) / 1000, 2) AS friday_close_over_busiest_strike,
    round(100.0 * sumIf(size, substring(ticker, 7, 6) = '260710') / sum(size), 1) AS friday_expiry_share_pct,
    back_expiry AS busiest_back_expiry_md,
    round(100.0 * sumIf(size, substring(ticker, 13, 1) = 'C' AND toFloat64(toUInt32OrZero(substring(ticker, 14, 8))) / 1000 > start_close) / sumIf(size, substring(ticker, 13, 1) = 'C'), 1) AS call_vol_strike_above_start_pct
FROM global_markets.options_trades
WHERE startsWith(ticker, 'O:NVDA') AND length(ticker) = 21
  AND sip_timestamp >= toDateTime64('2026-07-06 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-11 00:00:00', 9)

SELECT toString(date) AS d,
       round(sum(short_volume) / 1e6, 1) AS short_shares_m,
       count() AS nvda_rows_on_file
FROM global_markets.stocks_short_volume
WHERE ticker = 'NVDA' AND date >= toDate('2026-07-06') AND date <= toDate('2026-07-10')
GROUP BY date
ORDER BY date