3,256 answered market questions

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,
        max(date)                                      AS last_session
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND date >= today() - 30
    GROUP BY ticker
),
positions AS
(
    SELECT
        h.ticker                          AS ticker,
        h.shares                          AS shares,
        round(m.last_close, 2)            AS last_close,
        round(h.shares * m.last_close, 0) AS position_value,
        m.last_session                    AS last_session
    FROM holdings AS h
    INNER JOIN marks AS m ON m.ticker = h.ticker
)
SELECT
    ticker,
    shares,
    last_close,
    position_value,
    round(100 * position_value / sum(position_value) OVER (), 2) AS weight_pct,
    formatDateTime(last_session, '%b %e, %Y')                    AS priced_through
FROM positions
ORDER BY weight_pct DESC