SQL Window Functions | Dataford Interview Questions

How do you use SQL window functions to calculate running totals or rank users over time?

Use the provided user and transaction data to calculate monthly totals, each user's cumulative total across months, and each user's rank within the month. Include only users with transaction records, including months where their transaction amount is NULL.

Output

One row per user and active month.
Columns: user_id, user_name, activity_month, monthly_total, running_total, and monthly_rank.
Rank users by monthly total in descending order, with ties sharing a rank.
Order by activity_month, monthly_rank, and user_id.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
transaction_date	DATE	Date the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is SQL 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