SQL Window Functions for Portfolio Analysis | Dataford Interview Questions

Your question is SQL Window Functions for Portfolio Analysis. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write a SQL query using window functions such as RANK or PIVOT to analyze portfolio transactions.

Use the supplied portfolio and transaction data. Return monthly transaction totals for every portfolio, including portfolios without settled activity.

Output

One row per portfolio and month, with transaction_month, portfolio_id, portfolio_name, buy_value, sell_value, net_value, transaction_count, and portfolio_rank.
Include only settled BUY and SELL transactions in the calculations. Rank portfolios by descending monthly net_value, with ties sharing a rank.
Order by month ascending, rank ascending, and portfolio ID ascending.

Schema

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
portfolio_name	VARCHAR(100)	Portfolio name

portfolio_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
portfolio_id	INT	Portfolio associated with the transaction
transaction_ts	TIMESTAMP	Transaction timestamp
transaction_type	VARCHAR(20)	Transaction classification, such as BUY or SELL
amount	DECIMAL(14,2)	Transaction amount
status	VARCHAR(20)	Settlement status