SQL for Sudden Transaction Drops | Dataford Interview Questions

Write a query to identify users who have experienced a sudden drop in transaction frequency over the last 30 days compared to their historical average at Stripe.

Use April 2025 as the last 30-day period and January through March 2025 as the historical period. Consider only successful transactions. A user qualifies when their recent count is less than 50% of their historical average 30-day count and they have at least one historical transaction.

Output

One row per qualifying user with user_id, email, recent_transaction_count, historical_avg_30_day_count, and drop_ratio
Order by drop_ratio ascending, then user_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Stripe user identifier
email	VARCHAR(255)	User email address

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User who initiated the transaction
transaction_date	DATE	Transaction processing date
status	VARCHAR(20)	Transaction outcome, such as succeeded or failed
amount	NUMERIC(12,2)	Transaction amount in USD

Your question is SQL for Sudden Transaction Drops. 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