Window Functions Mastery | Dataford Interview Questions

Explain how you use SQL window functions like SUM and LEAD to solve analytics problems.

Write a PostgreSQL query that demonstrates both functions using the provided user and transaction data. Include only completed transactions and exclude users without completed activity.

Output

One row per user and reported calendar month.
Columns: user_id, user_name, month_start, monthly_amount, running_amount, and next_month_amount.
running_amount must accumulate monthly amounts per user, while next_month_amount must show the amount from the next reported month for that user.
Order by user_id, then month_start.

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 on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is Window Functions Mastery. 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