SQL Month-Over-Month Retention | Dataford Interview Questions

Unity Ads marketing analytics needs a month-over-month view of active player retention. Using transactional activity data, write a PostgreSQL query that measures how many users active in the previous month returned in each subsequent month.

A user is active in a month when they have at least one transaction with status = 'completed'. Only activity from January through April 2025 should be analyzed. Users must exist in the users table.

Requirements

Deduplicate users to one row per calendar month before calculating retention.
For each month with a prior month, return the retained active-user count and the prior month active-user count.
Calculate the retention rate as retained_users / prior_month_active_users * 100, rounded to two decimal places.
Sort results chronologically and exclude January because it has no prior month in the analysis period.

Schema

users

Column	Type	Description
user_idPK	INT	Unity player identifier
signup_date	DATE	Player signup date
acquisition_channel	VARCHAR(40)	Marketing acquisition source

transactions

Column	Type	Description
transaction_idPK	INT	Transaction identifier
user_id	INT	Player associated with the transaction
occurred_at	TIMESTAMP	Transaction timestamp
status	VARCHAR(20)	Transaction status
amount_usd	NUMERIC(10,2)	Transaction amount in USD

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