SQL for Customer Retention Segments | Dataford Interview Questions

Write a query to identify top-performing customer segments based on retention rates at Looker.

Use customers who signed up during January 2024 as the eligible cohort. A customer is retained if they have at least one activity record during February 2024. Exclude customers without a matching segment and segments with no eligible customers.

Output

One row per eligible customer segment with segment_name, eligible_customers, retained_customers, retention_rate, and segment_rank.
Retention rate is retained customers divided by eligible customers, rounded to four decimal places. Ties share a rank.
Order by segment_rank, then segment_name alphabetically.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
segment_id	INT	Customer segment identifier
signup_date	DATE	Date the customer signed up

customer_activity

Column	Type	Description
activity_idPK	INT	Unique activity identifier
customer_id	INT	Customer associated with the activity
activity_date	DATE	Date of customer activity

segments

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

Your question is SQL for Customer Retention Segments. 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