Rolling User Metric with Window Functions | Dataford Interview Questions

Write a SQL query using window functions to compute a rolling metric by user over time.

Use settled executions and calculate each user's rolling average PnL over the current execution and the two preceding settled executions. Exclude executions without a settled status.

Output

One row per settled execution, including user_id, user_name, executed_at, execution_id, pnl, and rolling_avg_pnl
Order by user_id, executed_at, and execution_id ascending

Schema

users

Column	Type	Description
user_idPK	VARCHAR(10)	Unique user identifier
user_name	VARCHAR(100)	Display name for the user

executions

Column	Type	Description
execution_idPK	INT	Unique execution identifier
user_id	VARCHAR(10)	User who generated the execution
executed_at	TIMESTAMP	Execution timestamp
status	VARCHAR(20)	Execution processing status
pnl	DECIMAL(12,2)	Profit or loss from the execution

Your question is Rolling User Metric 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