Week Over Week Engagement Drop | Dataford Interview Questions

Write a SQL query to identify users whose engagement dropped week over week.

Calculate weekly engagement from the recorded activity, compare each user's value with the immediately preceding week, and exclude users without a positive prior-week value.

Output

One row per user and week where engagement decreased
Columns: user_id, user_name, week_start, segment_name, current_engagement, previous_engagement, and percent_drop
Order by user_id, then week_start ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
user_type	VARCHAR(30)	User classification

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
user_id	INT	User associated with the event
event_date	DATE	Date of the engagement event
engagement_minutes	INT	Minutes of engagement recorded by the event
source	VARCHAR(40)	Source of the engagement event

user_segments

Column	Type	Description
segment_record_idPK	INT	Unique segment record identifier
user_id	INT	User assigned to the segment
segment_name	VARCHAR(40)	Segment label
effective_date	DATE	Date when the segment becomes effective
ended_date	DATE	Date when the segment ends

Your question is Week Over Week Engagement Drop. Start with the requirements and the three 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