SQL with Window Functions | Dataford Interview Questions

Describe a time you used SQL window functions to transform Robinhood-style data for analysis.

For this hands-on version, write a SQL query that summarizes eligible trades by user and calendar month, then ranks users within each account type and month. Ignore trades whose status is not FILLED or whose price is NULL.

Output

One row per user with eligible activity in a month.
Columns: account_type, trade_month, user_id, username, trades_count, monthly_traded_value, and monthly_rank.
Sort by account type, month, rank, and user ID.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(100)	User display name
account_type	VARCHAR(30)	Robinhood-style account classification

trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
user_id	INT	User who placed the trade
traded_at	TIMESTAMP	Timestamp when the trade was recorded
symbol	VARCHAR(20)	Security ticker symbol
side	VARCHAR(20)	Trade direction or transaction side
quantity	NUMERIC(14,2)	Number of shares or units
price	NUMERIC(14,2)	Execution price per unit
status	VARCHAR(20)	Trade processing status

Your question is SQL with Window Functions. Start with the requirements and the two tables in the Question tab.

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

Problem