Build Subscription Status Timeline | Dataford Interview Questions

Given a table of user subscription changes, write a query to construct a historical timeline of each user's subscription status.

Use subscription_changes; when multiple changes share a user and timestamp, the greatest change_id is authoritative. Consecutive records with the same status represent one interval.

Output

One row per status interval, with user_id, status, effective_from, effective_to, and is_current.
effective_to is NULL for the current interval.
Order by user_id, then effective_from, then change_id.

Schema

subscription_changes

Column	Type	Description
change_idPK	INT	Unique identifier for the subscription change
user_id	INT	Identifier of the user
changed_at	TIMESTAMP	Timestamp when the subscription status changed
subscription_status	VARCHAR(30)	Status recorded by the change event
change_source	VARCHAR(30)	System or workflow that recorded the change

Your question is Build Subscription Status Timeline. Start with the requirements and the one table 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