Portfolio Analysis in SQL: Weights to Drawdown

Portfolio analysis with free SQL needs two inputs you already have: the tickers you hold and the share count in each. Everything after that is arithmetic over daily price rows, so weights, concentration, pairwise correlation, trailing dividend income, and the worst peak-to-trough stretch all fall out of one price table plus one dividend table. The five queries below run on a six-name example book. To make it yours, edit the holdings list at the top of each one and run it again.

Portfolio analysis in SQL starts with position weights

A position weight is one holding's market value divided by the portfolio's total market value. Market value is share count times the most recent close. Those closes live in global_markets.stocks_daily_aggs, one row per ticker per session, with columns ticker, date, open, high, low, close, volume, vwap, and transactions. The latest mark per name is an argMax(close, (date, _ingest_time)), and the tuple tie-break keeps the pick deterministic when a session is restated.

The holdings sit in a short UNION ALL list at the top of the query. That list is the only part you edit, and it is plain SQL with no extensions, so it keeps working as columns get added around 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,
        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

The bars read the weight column; the table underneath shows the arithmetic that produced it. Priced through Oct 7, 2026, the largest position is AAPL at 25.71% of the book, and the smallest holding sits at 12.12%. All 6 names carry a fresh mark. Weights drift every session even when you never place a trade, which is the first thing a static spreadsheet of purchase amounts gets wrong.

How do I measure concentration risk from the weights?

Concentration asks how much of the book rides on its biggest names. Two numbers answer it. The first is the cumulative weight: sort the holdings from largest to smallest and keep a running total, so you can read off what the top one, two, or three cover. The second is the Herfindahl index, the sum of the squared percentage weights. A single-name portfolio scores 10,000. Six equal positions score about 1,667. Anything above that is the extra weight sitting in the leaders.

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

The top two holdings together cover 42.26% of the portfolio, and the finished book scores 1776 on the Herfindahl index. The running column is the useful part: it shows where the curve flattens, which is the point past which another name barely changes the picture. Our longer walk through the same measure, including how it behaves on a twenty-name book, is in concentration risk explained.

When are two holdings really one bet?

Weights treat every ticker as a separate decision. Correlation checks whether the market agrees. Take the daily simple return for each name, close divided by the prior session's close minus one, then run corr() over every pair across the trailing year of shared sessions. One wrinkle matters here: price columns arrive as Decimal(18,4), and the statistical aggregates reject that type, so the close is wrapped in toFloat64 before any of the maths.

WITH 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() - 420
      AND date <  today()
    GROUP BY ticker, date
),
rets AS
(
    SELECT
        ticker,
        date,
        close / lagInFrame(close) OVER (PARTITION BY ticker ORDER BY date
              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
    FROM px
)
SELECT
    concat(a.ticker, ' / ', b.ticker) AS pair,
    round(corr(a.ret, b.ret), 3)      AS correlation
FROM rets AS a
INNER JOIN rets AS b ON a.date = b.date
WHERE a.ticker < b.ticker
  AND a.date >= today() - 365
  AND isFinite(a.ret)
  AND isFinite(b.ret)
GROUP BY pair
ORDER BY correlation DESC

Six names make 15 pairs. The tightest over the trailing year is JNJ / KO at 0.385, and the loosest is KO / NVDA at -0.291. Read this panel next to the weights above and the point lands: a pair correlating near 0.9 at 12% each moves much more like one 24% position than like two independent ones, so the Herfindahl number understates how concentrated the book really is. The full grid version, with the formatting that makes a matrix readable, is in our stock correlation matrix in SQL.

How much dividend income does the blend pay?

Trailing twelve-month income is the sum of cash dividends with an ex-dividend date in the last 365 days, multiplied by share count. The global_markets.stocks_dividends table carries ticker, ex_dividend_date, cash_amount, currency, frequency, declaration_date, record_date, pay_date, and split_adjusted_cash_amount. Group by ticker and ex-date first, so a restated row cannot be counted twice.

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
),
divs AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(toFloat64(cash_amount)) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND currency = 'USD'
      AND ex_dividend_date >= today() - 365
      AND ex_dividend_date <  today()
    GROUP BY ticker, ex_dividend_date
),
ttm AS
(
    SELECT
        ticker,
        count()          AS payment_count,
        sum(cash_amount) AS dps
    FROM divs
    GROUP BY ticker
),
income AS
(
    SELECT
        h.ticker                             AS ticker,
        t.payment_count                      AS payment_count,
        round(t.dps, 4)                      AS ttm_dividend_per_share,
        round(h.shares * t.dps, 2)           AS ttm_income,
        round(100 * t.dps / m.last_close, 2) AS income_yield_pct
    FROM holdings AS h
    INNER JOIN ttm   AS t ON t.ticker = h.ticker
    INNER JOIN marks AS m ON m.ticker = h.ticker
)
SELECT
    ticker,
    payment_count,
    ttm_dividend_per_share,
    ttm_income,
    income_yield_pct,
    round(sum(ttm_income) OVER (), 2) AS blend_ttm_income
FROM income
ORDER BY ttm_income DESC

6 of the six holdings had an ex-dividend date in the trailing year. KO contributed the most at $630, and the blend collected $2055.8 in total. Two cautions travel with that figure. It is an ex-date count, not a cash-received count, so a dividend with an ex-date inside the window and a pay date after it is already included. And it looks backward: the per-share amounts are what a company has paid, with no claim on the next twelve months. The weighting arithmetic that turns these rows into a single book-level yield is in portfolio weighted dividend yield.

What was the portfolio's worst drawdown?

This is the number the free roundup tools skip. A drawdown is the percentage distance from the portfolio's highest value so far down to its value today. Build the blended daily series first, share count times each day's close, summed across holdings, keeping only sessions where all six names printed. Then carry a running maximum of that series and measure each day against it. The weekly minimum keeps the chart readable while preserving every trough.

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

The line hugging zero is a book making new highs; every dip away from it is money the portfolio had on paper and gave back. Over 53 weeks, the deepest reading on the curve is -7.48%, and the week ending Oct 5, 2026 sits at -0.63%. The running peak is seeded from 400 days of history rather than 365, so a high set just before the charted window still counts as the high. One detail worth repeating: this is the drawdown of today's holdings applied backward over the year, not of the trades you actually made.

What these queries do not cover

Four honest limits, stated here rather than discovered later.

• No tax lots or cost basis. Daily closes give market value, so there is nothing in these rows about what you paid, when, or what a sale would realise.
• No intraday marks. stocks_daily_aggs holds one close per session, which means a weight computed at 11 a.m. is still yesterday's weight.
• No delisted history. The holdings list is what you type today, so a name that left the market never appears, and a backward-looking drawdown built this way flatters any book that has survived.
• No corporate-action normalisation beyond what the vendor applies. Compare dividends across a split with split_adjusted_cash_amount rather than raw cash_amount, and sanity-check any price series that spans a split date.

FAQ

How do I calculate portfolio weights in SQL?

Multiply each holding's share count by its most recent close to get position value, then divide by the sum of all position values. A window function, sum(position_value) OVER (), gives you that denominator without a second pass over the data.

What is a good concentration measure for a portfolio?

Cumulative weight answers it in plain terms, as in "my top two names are half the book". The Herfindahl index compresses the same information into one score: 10,000 for a single position, about 1,667 for six equal ones, lower as the book spreads out.

How much history do I need for a correlation?

A trailing year of daily returns is roughly 250 observations, enough for a stable reading on liquid names. Shorter windows swing hard on a handful of sessions, and much longer ones blend together periods when the names behaved differently.

What is the difference between drawdown and volatility?

Volatility measures how much a series moves day to day in either direction. Drawdown measures only the distance from a prior peak, so it describes the loss an investor would have been sitting on at a given moment. A calm portfolio can still post a deep drawdown over a long slide.

Can I run these queries on my own brokerage holdings?

Yes. Replace the ticker and share count pairs in the UNION ALL block at the top of each query, and update the ticker list in the WHERE clause to match. Nothing else in the SQL is specific to the example book.

Every panel above ships with its full SQL underneath, so you can copy one, change six lines, and have the same analysis on your own book. The companion pieces cover the data source in the free SQL API for stock market data and the screening side in the relative volume screener. Run any of them in plain English on the Strasmore terminal.