Portfolio Value

ASKED IN INTERVIEW

4pts

Robinhood

Problem Statement

Robinhood needs to display the real-time total value of every user portfolio. The total value is calculated by taking the net shares owned for each ticker and multiplying it by the most recent market price for that ticker.

Write an SQL query to calculate the current total market value of each user portfolio.

Rules:

Net shares owned = Total shares bought - Total shares sold.
Use only the most recent price (based on the price_timestamp) for each ticker to calculate the value.
If a user net quantity for a stock is 0, it should not contribute to the portfolio value.
Portfolio value should be rounded to 2 decimal places.
Output columns: user_id, total_portfolio_value.
Sort the final result by total_portfolio_value in descending order.

Table Schema:

Transactions: transaction_id, user_id, ticker, transaction_type (BUY, SELL), quantity.
MarketPrices: price_id, ticker, current_price, price_timestamp.

Tests your understanding of

Joins, Aggregation, Window Functions and Subqueries

Input Tables

Transactions

user_id(INTEGER)	ticker(VARCHAR)	transaction_type(VARCHAR)	quantity(INTEGER)
1	AAPL	BUY	10
1	AAPL	SELL	2
1	TSLA	BUY	5
2	AAPL	BUY	15
2	BTC	BUY	1
3	TSLA	BUY	10
3	TSLA	SELL	10

MarketPrices

ticker(VARCHAR)	current_price(DECIMAL)	price_timestamp(TIMESTAMP)
AAPL	180	2026-02-11 09:00:00
AAPL	185.5	2026-02-11 10:00:00
TSLA	200	2026-02-11 10:00:00
BTC	45000	2026-02-11 10:00:00

Expected Output

user_id(INTEGER)	total_portfolio_value(DECIMAL)
2	47782.5
1	2484