Churn Risk from Streaming Drop | Dataford Interview Questions

How would you write a query to identify subscribers who are at risk of churning based on a sudden drop in their weekly streaming hours?

Assume a subscriber is at risk when their latest recorded week has streaming hours at least 50% below the immediately preceding recorded week. Use the provided tables and return only subscribers meeting that condition.

Output

One row per subscriber at risk
Include subscriber_id, subscriber_name, latest_week, previous_hours, latest_hours, and drop_percentage
Sort by drop_percentage descending, then subscriber_id ascending

Schema

subscribers

Column	Type	Description
subscriber_idPK	INT	Unique subscriber identifier
subscriber_name	VARCHAR(100)	Subscriber display name
plan_type	VARCHAR(30)	Current subscription plan

weekly_streaming_hours

Column	Type	Description
weekly_usage_idPK	INT	Unique weekly usage record identifier
subscriber_id	INT	Subscriber associated with the weekly measurement
week_start	DATE	Start date of the measurement week
streaming_hours	NUMERIC(8,2)	Total streaming hours during the week

Your question is Churn Risk from Streaming 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