مارکیٹ ری کیپ: 20 جولائی 2026 کا ہفتہ

SELECT ticker,
    round(argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00'), 2) AS prior_week_close,
    round(argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00' AND window_start < '2026-07-24 20:00:00'), 2) AS week_close,
    round((argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00' AND window_start < '2026-07-24 20:00:00')
         / argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00') - 1) * 100, 2) AS week_change_pct,
    round((argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
         / argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00') - 1) * 100, 2) AS prior_week_change_pct
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
  AND ((window_start >= '2026-07-10 13:30:00' AND window_start < '2026-07-10 20:00:00')
    OR (window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
    OR (window_start >= '2026-07-24 13:30:00' AND window_start < '2026-07-24 20:00:00'))
GROUP BY ticker
ORDER BY ticker

SELECT round(anyIf(ret, wk = toDate('2026-07-20')), 2) AS week_open_to_close_pct,
       arrayCount(x -> x > anyIf(ret, wk = toDate('2026-07-20')), groupArrayIf(ret, wk != toDate('2026-07-20'))) + 1 AS rank_best,
       count() AS weeks_compared,
       toString(min(wk)) AS first_week,
       anyIf(sessions_measured, wk = toDate('2026-07-20')) 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_measured,
           (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-21 00:00:00')
      AND window_start < toDateTime('2026-07-25 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_measured >= 3
)

SELECT toString(d) AS date,
       round(c, 2) AS spy_close,
       round((c / prev_c - 1) * 100, 2) AS change_pct,
       round(shares_m, 1) AS spy_shares_m
FROM (
    SELECT d, c, shares_m,
           lagInFrame(c) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_c
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMax(toFloat64(close), window_start) AS c,
               toFloat64(sum(volume)) / 1e6 AS shares_m
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND ((window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-24 20:00:00'))
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY d
    )
)
WHERE d >= '2026-07-20'
ORDER BY date

SELECT toString(d) AS date, advancers, decliners, unchanged, measured_names
FROM (
    SELECT d,
           countIf(c > prev_c) AS advancers,
           countIf(c < prev_c) AS decliners,
           countIf(c = prev_c) AS unchanged,
           count() AS measured_names
    FROM (
        SELECT d, c, dv,
               lagInFrame(c) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_c
        FROM (
            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-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
                OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-24 20:00:00'))
              AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
            GROUP BY ticker, d
        )
    )
    WHERE d >= '2026-07-20' AND prev_c > 0 AND dv >= 5000000
    GROUP BY d
)
ORDER BY date

SELECT
    countIf(cw > cp AND liquid) AS advancers,
    countIf(cw < cp AND liquid) AS decliners,
    countIf(cw = cp AND liquid) AS unchanged,
    countIf(liquid) AS measured_names,
    countIf(NOT liquid) AS dropped_by_liquidity_filter,
    round(100.0 * countIf(cw > cp AND liquid) / countIf(liquid), 1) AS advancer_pct
FROM (
    SELECT ticker, cp, cw, dv >= 5000000 AS liquid
    FROM (
        SELECT ticker,
               argMaxIf(toFloat64(close), window_start, window_start < '2026-07-18 00:00:00') AS cp,
               argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00') AS cw,
               sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-20 13:30:00') AS dv
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ((window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-25 00:00:00'))
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY ticker
        HAVING cp > 0 AND cw > 0
    )
)

SELECT sector, week_pct, round(max(week_pct) OVER () - week_pct, 2) AS points_behind_best
FROM (
    SELECT multiIf(ticker = 'XLK', 'Technology', ticker = 'XLC', 'Communications', ticker = 'XLE', 'Energy',
                   ticker = 'XLF', 'Financials', ticker = 'XLI', 'Industrials', ticker = 'XLB', 'Materials',
                   ticker = 'XLP', 'Staples', ticker = 'XLRE', 'Real Estate', ticker = 'XLU', 'Utilities',
                   ticker = 'XLV', 'Health Care', 'Consumer Discretionary') AS sector,
           round((cw / cp - 1) * 100, 2) AS week_pct
    FROM (
        SELECT ticker,
               argMaxIf(toFloat64(close), window_start, window_start < '2026-07-18 00:00:00') AS cp,
               argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00') AS cw
        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-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-24 13:30:00' AND window_start < '2026-07-24 20:00:00'))
        GROUP BY ticker
        HAVING cp > 0 AND cw > 0
    )
)
ORDER BY week_pct DESC

SELECT ticker, week_dollar_bn, round(100 * week_dollar_bn / max(week_dollar_bn) OVER (), 1) AS pct_of_leader
FROM (
    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-20 13:30:00' AND window_start < '2026-07-24 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 8
)
ORDER BY week_dollar_bn DESC

SELECT ticker, board, week_pct, week_dollar_m, sessions_traded,
       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, sessions_traded
    FROM (
        SELECT ticker,
               argMaxIf(toFloat64(close), window_start, window_start < '2026-07-18 00:00:00') AS cp,
               argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00') AS cw,
               sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-20 13:30:00') AS dv,
               uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), window_start >= '2026-07-20 13:30:00') AS sessions_traded
        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-17' AND execution_date <= '2026-07-24')
          AND ((window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-25 00:00:00'))
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        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, sessions_traded
    FROM (
        SELECT ticker,
               argMaxIf(toFloat64(close), window_start, window_start < '2026-07-18 00:00:00') AS cp,
               argMaxIf(toFloat64(close), window_start, window_start >= '2026-07-24 13:30:00') AS cw,
               sumIf(toFloat64(close) * toFloat64(volume), window_start >= '2026-07-20 13:30:00') AS dv,
               uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), window_start >= '2026-07-20 13:30:00') AS sessions_traded
        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-17' AND execution_date <= '2026-07-24')
          AND ((window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-25 00:00:00'))
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        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(toDate(sip_timestamp)) AS date,
       round(quantileExactIf(0.5)(
           10000 * (toFloat64(ask_price) - toFloat64(bid_price)) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2),
           bid_price > 0 AND ask_price > 0 AND ask_price >= bid_price), 2) AS median_spread_bps,
       round(count() / 1e6, 2) AS quote_updates_m,
       countIf(bid_price <= 0 OR ask_price <= 0) AS one_sided_quote_count,
       countIf(bid_price > ask_price AND bid_price > 0 AND ask_price > 0) AS crossed_quote_count
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'SPY'
  AND sip_timestamp >= '2026-07-20 13:30:00'
  AND sip_timestamp < '2026-07-24 20:00:00'
  AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
GROUP BY toDate(sip_timestamp)
HAVING countIf(bid_price > 0 AND ask_price > 0 AND ask_price >= bid_price) > 0
ORDER BY toDate(sip_timestamp)

SELECT toString(toDate(sip_timestamp)) AS date,
       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,
       round(100.0 * sumIf(size, substring(ticker, length(ticker) - 8, 1) = 'C') / sum(size), 1) AS pct_call,
       round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260724') / sum(size), 1) AS pct_expiring_jul24
FROM global_markets.options_trades
WHERE sip_timestamp >= '2026-07-20 00:00:00' AND sip_timestamp < '2026-07-25 00:00:00'
GROUP BY toDate(sip_timestamp)
ORDER BY toDate(sip_timestamp)

SELECT toString(date) AS date,
       round(toFloat64(yield_2_year), 2) AS yield_2y_pct,
       round(toFloat64(yield_10_year), 2) AS yield_10y_pct,
       round(toFloat64(yield_30_year), 2) AS yield_30y_pct,
       round((toFloat64(yield_10_year) - toFloat64(yield_2_year)) * 100) AS spread_2s10s_bp,
       round((toFloat64(yield_10_year) - (SELECT toFloat64(any(yield_10_year)) FROM global_markets.treasury_yields WHERE date = '2026-07-17')) * 100) AS chg_10y_from_prior_close_bp
FROM global_markets.treasury_yields
WHERE date >= '2026-07-17' AND date <= '2026-07-24'
ORDER BY date

SELECT date, tickers_on_file, short_shares_bn,
       round(100.0 * tickers_on_file / max(tickers_on_file) OVER (), 1) AS pct_of_fullest_file
FROM (
    SELECT toString(date) AS date,
           uniqExact(ticker) AS tickers_on_file,
           round(sum(short_shares) / 1e9, 2) AS short_shares_bn
    FROM (
        SELECT date, ticker, max(short_volume) AS short_shares
        FROM global_markets.stocks_short_volume
        WHERE date >= '2026-07-20' AND date <= '2026-07-24'
        GROUP BY date, ticker
    )
    GROUP BY date
)
ORDER BY date

SELECT toString(filing_date) AS date,
       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-20' AND filing_date <= '2026-07-24'
GROUP BY filing_date
ORDER BY date

SELECT
    (SELECT count() FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-20' AND filing_date <= '2026-07-24') AS filings_week,
    (SELECT uniqExact(filing_date) FROM global_markets.stocks_sec_edgar_index WHERE filing_date >= '2026-07-20' AND filing_date <= '2026-07-24') AS filing_days_on_file,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date >= '2026-07-20' AND ex_dividend_date <= '2026-07-24') AS ex_dividends_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-20' AND ex_dividend_date <= '2026-07-24') AS household_ex_dividends_week,
    (SELECT countIf(toFloat64(split_from) > toFloat64(split_to)) FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-20' AND execution_date <= '2026-07-24') AS reverse_splits_week,
    (SELECT countIf(toFloat64(split_to) > toFloat64(split_from)) FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-20' AND execution_date <= '2026-07-24') AS forward_splits_week,
    (SELECT count() FROM global_markets.stocks_ipos WHERE listing_date >= '2026-07-20' AND listing_date <= '2026-07-24') AS listings_week,
    (SELECT count() FROM global_markets.stocks_news WHERE published_utc >= '2026-07-20 04:00:00' AND published_utc < '2026-07-25 04:00:00') AS news_week,
    (SELECT uniqExact(JSONExtractString(publisher, 'name')) FROM global_markets.stocks_news WHERE published_utc >= '2026-07-20 04:00:00' AND published_utc < '2026-07-25 04:00:00') AS news_publishers_week

WITH spy AS (
    SELECT d, c, lagInFrame(c) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS p
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMax(toFloat64(close), window_start) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND ((window_start >= '2026-07-17 13:30:00' AND window_start < '2026-07-17 20:00:00')
            OR (window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-24 20:00:00'))
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY d
    )
)
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,
    count() AS regular_bars_in_week,
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date >= '2026-07-20' AND date <= '2026-07-24') AS holiday_rows_in_week,
    (SELECT countIf(c > p) FROM spy WHERE d >= '2026-07-20') AS up_sessions,
    (SELECT countIf(c < p) FROM spy WHERE d >= '2026-07-20') AS down_sessions,
    (SELECT toString(min(date)) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-24' AND date <= '2026-12-31' AND status = 'closed') AS next_closure_date,
    (SELECT argMin(name, date) FROM global_markets.stocks_market_holidays WHERE date > '2026-07-24' AND date <= '2026-12-31' AND status = 'closed') AS next_closure_name
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY'
  AND window_start >= '2026-07-20 13:30:00' AND window_start < '2026-07-24 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-27' AND date <= '2026-07-31' AND status != 'open') AS closures_next_week,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date >= '2026-07-27' AND ex_dividend_date <= '2026-07-31') 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-27' AND ex_dividend_date <= '2026-07-31') AS household_ex_div_next_week,
    (SELECT count() FROM global_markets.stocks_splits WHERE execution_date >= '2026-07-27' AND execution_date <= '2026-07-31') AS splits_next_week,
    (SELECT round(100.0 * sumIf(size, substring(ticker, length(ticker) - 14, 6) = '260731') / sum(size), 1) FROM global_markets.options_trades WHERE sip_timestamp >= '2026-07-24 00:00:00' AND sip_timestamp < '2026-07-25 00:00:00') AS jul31_expiry_pct_of_friday_volume,
    (SELECT toString(max(settlement_date)) FROM global_markets.stocks_short_interest WHERE settlement_date <= '2026-07-24') AS latest_short_interest_settlement