SQL for Volume Drop | Dataford Interview Questions

Write a SQL query to identify users whose transaction volume dropped by more than 30% week over week.

Assume transaction volume is the sum of amount per user per calendar week. Compare each week with the immediately preceding week, exclude users whose preceding-week volume is zero or unavailable, and treat missing current-week activity as zero.

Output

One row per qualifying user and week, with user_id, user_name, week_start, current_volume, prior_week_volume, and drop_pct.
Include only drops greater than 30%, ordered by week_start ascending and user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Wise user identifier
user_code	VARCHAR(20)	External user reference code
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
occurred_at	TIMESTAMP	Transaction timestamp
amount	NUMERIC(12,2)	Transaction amount used for volume calculations

Your question is SQL for Volume Drop. 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