SQL: Month-over-Month Retention | Dataford Interview Questions

Write a SQL query to calculate the month-over-month retention rate of users who completed an insurance quote on Insurify.

Use the provided users and quote_events tables. Treat a user as active in a month if they have at least one quote event with status completed in that calendar month.

Output

One row for each month with activity in the immediately preceding calendar month
Columns: month, prior_month_users, retained_users, and retention_rate
Report retention rate as a percentage rounded to two decimal places
Order by month ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Insurify user identifier
email	VARCHAR(255)	User email address

quote_events

Column	Type	Description
quote_event_idPK	INT	Unique quote event identifier
user_id	INT	User associated with the quote event
status	VARCHAR(30)	Quote event status
completed_at	TIMESTAMP	Timestamp when the quote was completed

Your question is SQL: Month-over-Month 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