Retention Rate SQL Query | Dataford Interview Questions

Remitly wants to measure how consistently newly registered customers complete transfers after signup. Write a PostgreSQL query to calculate monthly retention for the January 2024 signup cohort.

Requirements

Treat users who signed up from 2024-01-01 through 2024-01-31 as the cohort.
Calculate retention for months 0, 1, and 2, where month 0 is January 2024.
Count each user at most once per month, even if they complete multiple transfers.
Return the cohort size, retained users, and retention rate, using all cohort users as the denominator. Order by month number.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Remitly customer identifier
signup_date	DATE	Date the customer registered
country_code	VARCHAR(2)	Customer registration country

remitly_transfers

Column	Type	Description
transfer_idPK	INT	Unique transfer identifier
user_id	INT	Customer who initiated the transfer
completed_at	TIMESTAMP	Transfer completion timestamp
channel	VARCHAR(20)	Transfer initiation channel

Your question is Retention Rate SQL Query. 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