Weekly peak-to-trough drawdown of the blended portfolio

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
),
px AS
(
    SELECT
        ticker,
        date,
        max(toFloat64(close)) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND date >= today() - 400
      AND date <  today()
    GROUP BY ticker, date
),
daily AS
(
    SELECT
        p.date                      AS date,
        sum(h.shares * p.close)     AS portfolio_value,
        count()                     AS names_priced
    FROM px AS p
    INNER JOIN holdings AS h ON h.ticker = p.ticker
    GROUP BY p.date
    HAVING names_priced = 6
),
curve AS
(
    SELECT
        date,
        portfolio_value,
        max(portfolio_value) OVER (ORDER BY date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak
    FROM daily
),
weekly AS
(
    SELECT
        toMonday(date)                                            AS week_start,
        round(min(100 * (portfolio_value / running_peak - 1)), 2)  AS drawdown_pct
    FROM curve
    WHERE date >= today() - 365
    GROUP BY week_start
)
SELECT
    toString(week_start)                    AS week,
    drawdown_pct,
    round(min(drawdown_pct) OVER (), 2)     AS worst_drawdown_pct,
    formatDateTime(week_start, '%b %e, %Y') AS week_label
FROM weekly
ORDER BY week