Cohort Retention Rate SQL | Dataford Interview Questions

Affirm wants to measure how consistently customers return to originate additional loans after their first funded loan. Write a PostgreSQL query that calculates monthly retention for customer cohorts formed from January through March 2024.

Treat a customer as retained in a month when they have at least one funded Affirm loan during that month. Report months 0 through 5, where month 0 is the customer's cohort month.

Requirements

Identify each customer's first funded loan month and assign the customer to that monthly cohort.
Count distinct retained customers for every cohort and month offset from 0 through 5, including zero-retention months.
Return the cohort size and retention rate as a percentage, rounded to two decimal places.
Order results by cohort_month and months_since_cohort.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
signup_date	DATE	Date the customer signed up
acquisition_channel	VARCHAR(30)	Customer acquisition source

loans

Column	Type	Description
loan_idPK	INT	Unique loan identifier
customer_id	INT	Customer who originated the loan
originated_at	DATE	Loan origination date
status	VARCHAR(20)	Loan lifecycle status
amount	NUMERIC(10,2)	Loan principal amount

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