CTE Segmentation Query | Dataford Interview Questions

Write a SQL query using CTEs to analyze user activity and segment users by engagement level.

Use activity from April 1 through April 30, 2024. Return every user, including users without activity. Calculate active days, activity events, distinct event types, the latest activity timestamp, and an engagement score based on these metrics.

Output

One row per user with user_id, user_name, active_days, activity_events, distinct_event_types, last_activity_at, engagement_score, and engagement_level.
Assign four relative engagement levels using score quartiles, with ties broken by user_id.
Order by engagement score descending, then user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Display name of the user

user_activity

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of activity event
event_at	TIMESTAMP	Timestamp when the activity occurred

Your question is CTE Segmentation Query. 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