Top Segments by Retention Rate | Dataford Interview Questions

Write a SQL query to identify the top 5 user segments by retention rate.

Use 30-day retention, defined as a user completing activity during the 30th through 60th day after signup. Include users whose signup date is on or before 2025-02-28 so every user has a complete retention window.

Output

One row per segment, with segment_name, eligible_users, retained_users, and retention_rate.
Include only segments with at least one eligible user.
Return the five highest retention rates first, breaking ties by more eligible users and then alphabetically by segment name.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
segment_id	INT	Assigned user segment
signup_date	DATE	Date the user signed up

segments

Column	Type	Description
segment_idPK	INT	Unique segment identifier
segment_name	VARCHAR(100)	User segment name

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Date of user activity

Your question is Top Segments by Retention Rate. 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