Cumulative weight and the Herfindahl concentration index

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 holdings AS
(
    SELECT 'AAPL' AS ticker, 120 AS shares
    UNION ALL SELECT 'MSFT', 45
    UNION ALL SELECT 'NVDA', 80
    UNION ALL SELECT 'KO',   300
    UNION ALL SELECT 'JNJ',  90
    UNION ALL SELECT 'XOM',  150
),
marks AS
(
    SELECT
        ticker,
        argMax(toFloat64(close), (date, _ingest_time)) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND date >= today() - 30
    GROUP BY ticker
),
weights AS
(
    SELECT
        h.ticker AS ticker,
        round(100 * (h.shares * m.last_close)
              / sum(h.shares * m.last_close) OVER (), 4) AS weight_pct
    FROM holdings AS h
    INNER JOIN marks AS m ON m.ticker = h.ticker
)
SELECT
    ticker,
    round(weight_pct, 2) AS weight_pct,
    round(sum(weight_pct) OVER (ORDER BY weight_pct DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_weight_pct,
    round(sum(pow(weight_pct, 2)) OVER (ORDER BY weight_pct DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS cumulative_hhi
FROM weights
ORDER BY weight_pct DESC