Lead and Lag for Churn | Dataford Interview Questions

How would you use SQL window functions like LEAD and LAG to analyze user churn over time?

Use the users and activity_events tables. Return one row for each user and month in which the user was active, identifying the previous and next active months and whether the user churned after that month.

Output

Columns: user_id, active_month, previous_active_month, next_active_month, and churned_after_month
Treat a missing next active month or a gap of more than one calendar month as churn
Order by user_id, then active_month

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user registered

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the event
event_at	TIMESTAMP	Timestamp when the activity occurred
event_type	VARCHAR(50)	Optional activity category

Your question is Lead and Lag for Churn. 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