SQL Usage Drop Analysis | Dataford Interview Questions

Write a SQL query to identify customers whose usage has dropped by more than twenty percent month-over-month.

Use the customers and usage_events tables. Compare each customer's total usage for a calendar month with the immediately preceding calendar month. Exclude customers without usage in both months and cases where the prior month's usage is zero or unavailable.

Output

One row per customer and current usage month meeting the decline condition.
Columns: customer_id, customer_name, current_month, prior_month_usage, current_month_usage, and drop_percentage.
Include only declines greater than 20%, ordered by customer_id and current_month ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(150)	Customer display name

usage_events

Column	Type	Description
usage_event_idPK	INT	Unique usage event identifier
customer_id	INT	Customer associated with the usage event
usage_date	DATE	Date on which usage was recorded
usage_units	INTEGER	Units consumed during the event

Your question is SQL Usage Drop Analysis. 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