SQL for MoM Retention | Dataford Interview Questions

Thumbtack’s Marketing Analytics team wants to measure how consistently service professionals who joined the platform in 2024 remain active. Write a PostgreSQL query that calculates month-over-month retention by join-month cohort.

Requirements

Include only service professionals whose joined_at date falls in 2024.
Treat a professional as active in a month when at least one activity record exists during that month.
For each cohort and activity month after the join month, return the previous-month active count, retained-professional count, and retention rate. Define retention as retained professionals divided by professionals active in the previous month.
Deduplicate multiple activity records for the same professional in the same month and order results by cohort month and activity month.

Schema

service_professionals

Column	Type	Description
professional_idPK	INT	Unique service professional identifier
professional_name	VARCHAR(100)	Professional display name
joined_at	DATE	Date the professional joined Thumbtack
city	VARCHAR(80)	Primary service city
specialty	VARCHAR(100)	Primary service category

professional_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
professional_id	INT	References service_professionals.professional_id
activity_at	DATE	Date of activity on Thumbtack
activity_type	VARCHAR(40)	Type of professional activity

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