SQL Retention Over Six Months | Dataford Interview Questions

Thumbtack wants to measure whether service professionals who completed a job during January 2025 continued completing jobs over the following five months. Write a PostgreSQL query that calculates monthly retention for this January cohort.

Requirements

Define the cohort as distinct professionals with a completed job from January 1 through January 31, 2025.
Return one row for each month from January through June 2025, including the cohort month.
Calculate retained professionals, the fixed cohort size, and retention rate as a percentage. Count a professional once per month, even if they completed multiple jobs.
Include months with no activity and order the output chronologically.

Schema

professionals

Column	Type	Description
professional_idPK	INT	Unique Thumbtack service professional identifier
service_category	VARCHAR(50)	Primary service category listed by the professional
onboarded_at	DATE	Date the professional joined Thumbtack

jobs

Column	Type	Description
job_idPK	INT	Unique job identifier
professional_id	INT	Professional assigned to the job
status	VARCHAR(20)	Current job status
completed_at	TIMESTAMP	Timestamp when the job was completed

Your question is SQL Retention Over Six Months. 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