A 10 percent index cap, worked on a 40 stock basket

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH basket AS (
    SELECT
        ticker,
        toFloat64(argMax(market_cap, date)) AS mcap,
        max(date)                           AS asof
    FROM global_markets.stocks_ratios
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','AVGO','TSLA','LLY','JPM',
                     'V','MA','XOM','UNH','JNJ','PG','HD','COST','ABBV','WMT',
                     'NFLX','BAC','KO','MRK','PEP','ADBE','CSCO','AMD','TMO','ACN',
                     'MCD','ABT','CRM','LIN','PFE','INTU','TXN','QCOM','AMGN','ORCL')
      AND date >= today() - 60
      AND market_cap > 0
    GROUP BY ticker
),
weights AS (
    SELECT
        ticker,
        asof,
        100 * mcap / sum(mcap) OVER () AS w0
    FROM basket
),
spill AS (
    SELECT
        sum(greatest(w0 - 10, 0))              AS excess,
        sum(if(w0 < 10, w0, 0))                AS room,
        formatDateTime(max(asof), '%b %e, %Y') AS as_of
    FROM weights
)
SELECT
    ticker,
    round(w0, 2)                                                AS uncapped_weight_pct,
    round(if(w0 >= 10, 10.0, w0 * (1 + excess / room)), 2)      AS capped_weight_pct,
    round(if(w0 >= 10, 10.0, w0 * (1 + excess / room)) - w0, 2) AS cap_shift_pp,
    as_of
FROM weights
CROSS JOIN spill
ORDER BY uncapped_weight_pct DESC
LIMIT 12