Rolling 30-Day Retention Query | Dataford Interview Questions

Write a query to calculate the rolling 30-day retention rate for our top-tier legal firm customers.

Use the customers and customer_activity tables. For each date on which a top-tier customer was active, calculate the percentage of that date's top-tier active customers who were active again during the following 30 days.

Output

One row per qualifying activity date
Columns: activity_date, active_customers, retained_customers, and retention_rate
Include dates with zero retained customers, order by activity_date ascending, and report retention_rate as a percentage rounded to two decimal places

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique legal firm customer identifier
firm_name	VARCHAR(255)	Legal firm name
customer_tier	VARCHAR(50)	Customer service tier

customer_activity

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

Your question is Rolling 30-Day Retention 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