סיכום שוק שבוע 6 ביולי 2026

SELECT ticker,
    round(argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-02') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS prior_week_close,
    round(argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-07-06') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199), 2) AS week_close,
    round((argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2026-07-06') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)
         / argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-02') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - 1) * 100, 1) AS week_change_pct,
    round((argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-02') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)
         / argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-26') AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) - 1) * 100, 1) AS prior_week_change_pct
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
  AND window_start >= '2026-06-22 13:30:00' AND window_start < '2026-07-10 20:00:00'
GROUP BY ticker
ORDER BY ticker

SELECT round(anyIf(ret, wk = toDate('2026-07-06')), 2) AS week_open_to_close_pct,
       arrayCount(x -> x > anyIf(ret, wk = toDate('2026-07-06')), groupArrayIf(ret, wk != toDate('2026-07-06'))) + 1 AS rank_best,
       count() AS weeks_compared,
       toString(min(wk)) AS first_week,
       anyIf(sessions, wk = toDate('2026-07-06')) AS sessions_this_week
FROM (
    SELECT toStartOfWeek(toDate(toTimeZone(window_start, 'America/New_York')), 1) AS wk,
           uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions,
           (argMax(toFloat64(close), window_start) / argMin(toFloat64(open), window_start) - 1) * 100 AS ret
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2025-07-06 00:00:00')
      AND window_start < toDateTime('2026-07-11 00:00:00')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY wk
    HAVING sessions >= 3
)

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 spy_close,
    round((argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199) / any(prev) - 1) * 100, 1) AS change_pct,
    round(toFloat64(sum(volume)) / 1e6, 1) AS spy_shares_m
FROM global_markets.delayed_stocks_minute_aggs
INNER JOIN (
    SELECT d, lagInFrame(c) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev
    FROM (
        SELECT 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 = 'SPY' AND window_start >= toDateTime('2026-07-02 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
        GROUP BY d
    )
) AS p ON toDate(toTimeZone(window_start, 'America/New_York')) = p.d
WHERE ticker = 'SPY' AND window_start >= toDateTime('2026-07-06 00:00:00') AND window_start < toDateTime('2026-07-11 00:00:00')
GROUP BY et_date
ORDER BY et_date

SELECT ticker, round((c7 / c6 - 1) * 100, 1) AS day_pct
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-06 20:00:00')) AS c6,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-07 13:30:00' AND window_start < '2026-07-07 20:00:00')) AS c7
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('INTC', 'TER')
      AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-07 20:00:00'
    GROUP BY ticker
)
ORDER BY ticker

SELECT toDate(toTimeZone(published_utc, 'America/New_York')) AS d,
       countIf(has(tickers, 'NVDA')) AS nvda_articles,
       countIf(has(tickers, 'MU')) AS mu_articles,
       countIf(has(tickers, 'INTC')) AS intc_articles,
       countIf(has(tickers, 'TER')) AS ter_articles
FROM global_markets.stocks_news
WHERE published_utc >= '2026-07-06 00:00:00' AND published_utc < '2026-07-11 04:00:00'
  AND toDate(toTimeZone(published_utc, 'America/New_York')) >= '2026-07-06'
  AND toDate(toTimeZone(published_utc, 'America/New_York')) <= '2026-07-10'
GROUP BY d
ORDER BY d

SELECT toString(toDate(toTimeZone(published_utc, 'America/New_York'))) AS session,
       JSONExtractString(publisher, 'name') AS outlet,
       title AS headline
FROM global_markets.stocks_news
WHERE toDate(toTimeZone(published_utc, 'America/New_York')) IN ('2026-07-07', '2026-07-08')
  AND JSONExtractString(publisher, 'name') = 'The Motley Fool'
  AND (positionCaseInsensitive(title, 'Burry') > 0 OR positionCaseInsensitive(title, 'Sandisk Stock Pop') > 0)
ORDER BY session, title

SELECT
    countIf(cw > cp AND cp > 0 AND dv >= 5000000) AS advancers,
    countIf(cw < cp AND cp > 0 AND dv >= 5000000) AS decliners,
    countIf(cp > 0 AND cw > 0 AND dv >= 5000000) AS measured_names,
    round(100.0 * countIf(cw > cp AND cp > 0 AND dv >= 5000000) / countIf(cp > 0 AND cw > 0 AND dv >= 5000000), 1) AS advancer_pct
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start < '2026-07-03 00:00:00')) AS cp,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00')) AS cw,
           sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 13:30:00') AS dv
    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-11 00:00:00'))
    GROUP BY ticker
)

SELECT ticker, round((cw / cp - 1) * 100, 1) AS week_pct
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start < '2026-07-03 00:00:00')) AS cp,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')) AS cw
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('XLB', '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-10 13:30:00' AND window_start < '2026-07-10 20:00:00'))
    GROUP BY ticker
)
ORDER BY ticker

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

SELECT ticker,
       round((cw / cp - 1) * 100, 1) AS week_pct
FROM (
    SELECT ticker,
           toFloat64(argMaxIf(close, window_start, window_start < '2026-07-03 00:00:00')) AS cp,
           toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')) AS cw
    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-02 13:30:00' AND window_start < '2026-07-02 20:00:00')
        OR (window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'))
    GROUP BY ticker
)
ORDER BY ticker

WITH ipo AS (
    SELECT toFloat64(final_issue_price) AS offer, toFloat64(total_offer_size) AS raise
    FROM global_markets.stocks_ipos
    WHERE issuer_name = 'SK Hynix Inc' AND listing_date = '2026-07-10'
    LIMIT 1
),
debuts AS (
    SELECT a.ticker AS t, sum(toFloat64(a.close) * toFloat64(a.volume)) AS debut_rth_dollars
    FROM global_markets.delayed_stocks_minute_aggs a
    INNER JOIN (
        SELECT multiIf(replaceAll(ticker, ' ', '') = 'SKHY', 'SKHYV', replaceAll(ticker, ' ', '')) AS tk, listing_date
        FROM global_markets.stocks_ipos
        WHERE listing_date >= '2026-01-01' AND listing_date <= '2026-07-10'
    ) l ON a.ticker = l.tk
    WHERE a.window_start >= '2026-01-01 00:00:00' AND a.window_start < '2026-07-11 00:00:00'
      AND toDate(a.window_start) = l.listing_date
      AND (toHour(a.window_start) * 60 + toMinute(a.window_start)) BETWEEN 810 AND 1199
    GROUP BY a.ticker
)
SELECT
    round((SELECT offer FROM ipo), 2) AS offer_px,
    round((SELECT raise FROM ipo) / 1e9, 1) AS raised_bn,
    (SELECT countIf(toFloat64(total_offer_size) > (SELECT raise FROM ipo)) FROM global_markets.stocks_ipos WHERE listing_date >= '2026-01-01' AND listing_date <= '2026-07-10') AS bigger_2026_raises,
    round((SELECT max(toFloat64(total_offer_size)) FROM global_markets.stocks_ipos WHERE listing_date >= '2026-01-01' AND listing_date <= '2026-07-10') / 1e9, 1) AS biggest_2026_raise_bn,
    round(toFloat64(argMinIf(open, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')), 2) AS first_rth_px,
    toHour(toTimeZone(minIf(window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'), 'America/New_York')) * 60
        + toMinute(toTimeZone(minIf(window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'), 'America/New_York')) AS first_rth_bar_et_minute,
    formatDateTime(toTimeZone(minIf(window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'), 'America/New_York'), '%H:%i') AS first_rth_bar_et,
    round(toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')), 2) AS debut_close,
    round((toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')) / (SELECT offer FROM ipo) - 1) * 100, 1) AS close_vs_offer_pct,
    round(toFloat64(argMinIf(open, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00'))
        - toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')), 2) AS faded_from_first_print,
    round(sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00') / 1e9, 2) AS rth_dollar_bn,
    (SELECT count() FROM debuts) AS debuts_measured_2026,
    (SELECT countIf(debut_rth_dollars > (SELECT debut_rth_dollars FROM debuts WHERE t = 'SKHYV')) + 1 FROM debuts) AS debut_dollar_rank_2026,
    round((SELECT max(debut_rth_dollars) FROM debuts) / 1e9, 1) AS biggest_2026_debut_bn
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SKHYV' AND window_start >= '2026-07-10 04:00:00' AND window_start < '2026-07-11 00:00:00'

SELECT ticker, board, week_pct, week_dollar_m,
       round(100 * week_dollar_m / max(week_dollar_m) OVER (), 1) AS pct_of_dollar_max
FROM (
    SELECT 'gainers' AS board, ticker, round((cw / cp - 1) * 100, 1) AS week_pct, round(dv / 1e6, 1) AS week_dollar_m
    FROM (
        SELECT ticker,
               toFloat64(argMaxIf(close, window_start, window_start < '2026-07-03 00:00:00')) AS cp,
               toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')) AS cw,
               sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 13:30:00') AS dv
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker NOT IN ('SPCX')
          AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-06' AND execution_date <= '2026-07-10')
          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-11 00:00:00'))
        GROUP BY ticker
        HAVING cp > 0 AND cw > 0 AND dv >= 5000000
    )
    ORDER BY week_pct DESC
    LIMIT 10
    UNION ALL
    SELECT 'decliners' AS board, ticker, round((cw / cp - 1) * 100, 1) AS week_pct, round(dv / 1e6, 1) AS week_dollar_m
    FROM (
        SELECT ticker,
               toFloat64(argMaxIf(close, window_start, window_start < '2026-07-03 00:00:00')) AS cp,
               toFloat64(argMaxIf(close, window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')) AS cw,
               sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-06 13:30:00') AS dv
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker NOT IN ('SPCX')
          AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-06' AND execution_date <= '2026-07-10')
          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-11 00:00:00'))
        GROUP BY ticker
        HAVING cp > 0 AND cw > 0 AND dv >= 5000000
    )
    ORDER BY week_pct ASC
    LIMIT 10
)
ORDER BY board DESC, abs(week_pct) DESC

SELECT toString(date) AS d,
       uniqExact(ticker) AS tickers_on_file,
       round(sum(short_volume) / 1e9, 2) AS short_shares_bn
FROM global_markets.stocks_short_volume
WHERE date >= '2026-07-06' AND date <= '2026-07-10'
GROUP BY date
ORDER BY date

SELECT toDate(sip_timestamp) AS d,
       round(toFloat64(sum(size)) / 1e6, 1) AS contracts_m,
       round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = formatDateTime(toDate(sip_timestamp), '%y%m%d')) / sum(size), 1) AS pct_0dte
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-11 00:00:00'
GROUP BY d
ORDER BY d

SELECT
    round((toFloat64(anyIf(yield_2_year, date = '2026-07-10')) - toFloat64(anyIf(yield_2_year, date = '2026-07-02'))) * 100) AS chg_2y_bp,
    round((toFloat64(anyIf(yield_10_year, date = '2026-07-10')) - toFloat64(anyIf(yield_10_year, date = '2026-07-02'))) * 100) AS chg_10y_bp,
    round((toFloat64(anyIf(yield_30_year, date = '2026-07-10')) - toFloat64(anyIf(yield_30_year, date = '2026-07-02'))) * 100) AS chg_30y_bp,
    round(toFloat64(anyIf(yield_10_year, date = '2026-07-10')), 2) AS y10_fri_pct,
    round(toFloat64(anyIf(yield_30_year, date = '2026-07-10')), 2) AS y30_fri_pct,
    (SELECT count() FROM global_markets.treasury_yields WHERE date IN ('2026-07-09', '2026-07-10')) AS jul9_jul10_print_rows
FROM global_markets.treasury_yields
WHERE date IN ('2026-07-02', '2026-07-10')

SELECT
    (SELECT count() FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-06' AND filing_date <= '2026-07-10') AS filings_week,
    (SELECT count() FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-10') AS filings_friday,
    toInt64((SELECT count() FROM global_markets.stocks_sec_edgar_index WHERE filing_date = '2026-07-06'))
        - toInt64((SELECT max(c) FROM (SELECT count() AS c FROM global_markets.stocks_sec_edgar_index WHERE filing_date > '2026-07-06' AND filing_date <= '2026-07-10' GROUP BY filing_date))) AS monday_minus_next_busiest,
    (SELECT round(countIf(form_type = '4' AND filing_date = '2026-07-06') / countIf(form_type = '4' AND filing_date = '2026-07-10'), 1) FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-06' AND filing_date <= '2026-07-10') AS monday_over_friday_form4,
    (SELECT min(c) FROM (SELECT count() AS c FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-06' AND filing_date <= '2026-07-10' AND form_type = '424B2' GROUP BY filing_date)) AS min_daily_424b2,
    (SELECT max(c) FROM (SELECT count() AS c FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-06' AND filing_date <= '2026-07-10' AND form_type = '424B2' GROUP BY filing_date)) AS max_daily_424b2,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date >= '2026-07-06' AND ex_dividend_date <= '2026-07-10') AS ex_dividends_week,
    (SELECT countIf(toFloat64(split_from) > toFloat64(split_to)) FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-06' AND execution_date <= '2026-07-10') AS reverse_splits_week,
    (SELECT countIf(toFloat64(split_to) > toFloat64(split_from)) FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-06' AND execution_date <= '2026-07-10') AS forward_splits_week,
    (SELECT count() FROM global_markets.stocks_news WHERE toDate(toTimeZone(published_utc, 'America/New_York')) >= '2026-07-06'
        AND toDate(toTimeZone(published_utc, 'America/New_York')) <= '2026-07-10') AS news_week

SELECT toString(filing_date) AS d,
       count() AS filings,
       countIf(form_type = '4') AS form4,
       countIf(form_type = '8-K') AS form8k,
       countIf(form_type = '424B2') AS f424b2
FROM global_markets.stocks_sec_edgar_index
WHERE filing_date >= '2026-07-06' AND filing_date <= '2026-07-10'
GROUP BY filing_date
ORDER BY filing_date

SELECT toString(filing_date) AS filing_date,
       form_type
FROM global_markets.stocks_sec_edgar_index
WHERE issuer_name = 'SK hynix Inc.'
  AND filing_date >= '2026-06-01' AND filing_date <= '2026-07-10'
ORDER BY filing_date ASC, form_type ASC

SELECT
    uniqExact(toDate(toTimeZone(window_start, 'America/New_York'))) AS sessions_in_week,
    toString(min(toDate(toTimeZone(window_start, 'America/New_York')))) AS first_session,
    toString(max(toDate(toTimeZone(window_start, 'America/New_York')))) AS last_session,
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date >= '2026-07-06' AND date <= '2026-07-10') AS holiday_rows_in_week,
    (SELECT toString(min(date)) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-10' AND status = 'closed') AS next_closure_date,
    (SELECT argMin(name, date) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-10' AND status = 'closed') AS next_closure_name
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY'
  AND window_start >= '2026-07-06 13:30:00' AND window_start < '2026-07-10 20:00:00'
  AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199

SELECT
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date >= '2026-07-11' AND date <= '2026-07-17' AND status != 'open') AS closures_next_week,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date >= '2026-07-13' AND ex_dividend_date <= '2026-07-17') AS ex_dividends_next_week,
    (SELECT countIf(ticker IN ('AAPL', 'MSFT', 'JPM', 'KO', 'JNJ', 'XOM', 'CVX', 'PG', 'WMT', 'HD')) FROM global_markets.stocks_dividends WHERE ex_dividend_date >= '2026-07-13' AND ex_dividend_date <= '2026-07-17') AS household_ex_div_next_week,
    (SELECT round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260717') / sum(size), 1) FROM global_markets.options_trades WHERE sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-11 00:00:00') AS jul17_expiry_pct_of_week_volume