Daily Portfolio Value from Prices and Holdings
You are given two SQL tables.
prices(cur_date, ticker, price) -- the closing price of each ticker on each date.
portfolios(cur_date, portfolio, ticker, shares) -- how many shares of each ticker each portfolio held on each date.
Write a single query that returns, for every (date, portfolio) pair, the total mark-to-market value of that portfolio on that date. The value of a holding is price * shares, summed over all tickers the portfolio held that day. Output columns: cur_date, portfolio, and the portfolio value PV. Order the result by cur_date ascending, then portfolio ascending.
Constraints
- A portfolio may hold many tickers on a given date.
- Every (date, ticker) in
portfolioshas a matching row inprices.
Example
prices: (2024-01-02, AAPL, 190), (2024-01-02, MSFT, 400)
portfolios: (2024-01-02, FundA, AAPL, 10), (2024-01-02, FundA, MSFT, 5)
Output: (2024-01-02, FundA, 3900) since 190*10 + 400*5 = 1900 + 2000 = 3900.
Hints
- You need two pieces of information per holding: how many shares and at what price. They live in two different tables, so the first move is a join.
- Be careful about the join key. A ticker has a different price on every date, so matching on ticker alone double-counts. Join on the pair $(\text{cur\\_date}, \text{ticker})$.
- After joining, aggregate with $\text{SUM}(\text{price} \times \text{shares})$ and $\text{GROUP BY}$ on $(\text{cur\\_date}, \text{portfolio})$ so you get exactly one value per portfolio per day.
Worked Solution
How to Think About It: This is the bread-and-butter join every quant developer writes on day one: combine a holdings table with a price table, multiply, and aggregate. The brute-force mental model is a nested loop -- for each portfolio row, look up the matching price -- but SQL does that join for you. The only two things to get right are the JOIN key (you must match on BOTH date AND ticker, not just ticker) and the GROUP BY granularity (one row per date-portfolio pair).
Algorithm: Join portfolios to prices on (cur_date, ticker). For each joined row compute price * shares. Sum that product within each (cur_date, portfolio) group. Sort the output.
Code:
```sql SELECT prices.cur_date, portfolios.portfolio, SUM(prices.price * portfolios.shares) AS PV FROM prices JOIN portfolios ON prices.cur_date = portfolios.cur_date AND prices.ticker = portfolios.ticker GROUP BY prices.cur_date, portfolios.portfolio ORDER BY prices.cur_date ASC, portfolios.portfolio ASC; ```
Complexity: With an index on the join keys, the join is roughly $O(N)$ in the number of holding rows; the aggregation is linear in the joined size.
Answer: Inner-join on (cur_date, ticker), SUM(price * shares) grouped by (cur_date, portfolio), ordered by date then portfolio.
Intuition
Computing a portfolio's net asset value by multiplying positions against a price snapshot and summing is the single most common operation in fund accounting and risk systems. The subtle trap that separates a clean answer from a buggy one is the join granularity: prices are indexed by both date and instrument, so a careless join on instrument alone silently fans out every holding against every historical price and inflates the answer. In production this same pattern scales to thousands of portfolios and millions of price points, which is why the join keys are almost always indexed and why correctness here is non-negotiable -- a wrong NAV feeds straight into P&L and margin.