Retention Cohort SQL | Dataford Interview Questions

Your question is Retention Cohort SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Rippling Operations needs a monthly retention cohort view for active platform accounts using account activation and activity records. Write a PostgreSQL query that shows how many accounts from each activation cohort were active in each subsequent month.

Requirements

Include only accounts whose current status is active and whose activation date is known.
Define each account's cohort as the month of activated_at.
Count each account at most once per activity month, even if it has multiple activity records.
Return the cohort size, retained account count, month number relative to activation, and retention rate, ordered by cohort and month number.

Schema

accounts

Column	Type	Description
account_idPK	INTEGER	Unique platform account identifier
account_name	VARCHAR(100)	Platform account name
activated_at	DATE	Date the account became active
status	VARCHAR(20)	Current account status

account_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity record identifier
account_id	INTEGER	Related account identifier
activity_at	DATE	Date of account activity