Price weight vs cap weight across a stand-in 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 members AS
(
    SELECT
        ticker,
        argMax(toFloat64(price), date)      AS last_price,
        argMax(toFloat64(market_cap), date) AS last_cap
    FROM global_markets.stocks_ratios
    WHERE ticker IN ('AAPL','AMGN','AXP','CAT','GS','HD','JNJ','KO','MCD','MSFT','V','WMT')
      AND date >= today() - 45
      AND price > 0
      AND market_cap > 0
    GROUP BY ticker
),
totals AS
(
    SELECT
        sum(last_price) AS price_sum,
        sum(last_cap)   AS cap_sum
    FROM members
)
SELECT
    m.ticker                                    AS symbol,
    round(m.last_price, 2)                      AS share_price,
    round(100 * m.last_price / t.price_sum, 2)  AS price_weight_pct,
    round(100 * m.last_cap / t.cap_sum, 2)      AS cap_weight_pct
FROM members AS m
CROSS JOIN totals AS t
ORDER BY price_weight_pct DESC