Six-Month Renter Retention by Move-In Cohort — SQL Problem
A rental platform measures how many renters are still renting through it six months after they first moved in.
- Difficulty: Hard
- Topics: Dates, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #372
Problem statement
A rental platform measures how many renters are still renting through it six months after they first moved in. A renter's cohort is the month ('YYYY-MM') of their earliest lease start_date. A renter is retained when some lease of theirs — in any flat, since people move — covers the date exactly 6 months after that first start (start_date ≤ that date, and end_date on or after it or NULL).
Return cohort_month, renters, retained and retention_pct = 100 × retained ÷ renters rounded to 1 decimal, ordered by cohort_month.
Tables
Table: RenterLease
| Column | Type |
|---|---|
| lease_id | int |
| renter_id | int |
| flat_no | varchar |
| start_date | date |
| end_date | date |
Primary key: lease_id.
end_date is the last day of the lease, NULL while it runs. A renter can hold several leases over time.
Examples
Example 1
RenterLease
| lease_id | renter_id | flat_no | start_date | end_date |
|---|---|---|---|---|
| 1 | 101 | BN-101 | 2024-01-10 | 2024-07-09 |
| 2 | 101 | WK-201 | 2024-07-10 | NULL |
| 3 | 102 | KH-305 | 2024-01-20 | 2024-06-30 |
| 4 | 103 | AU-110 | 2024-01-05 | 2024-07-05 |
| 5 | 104 | BN-102 | 2024-02-01 | 2025-01-31 |
| 6 | 105 | HN-404 | 2024-02-14 | 2024-05-13 |
| 7 | 105 | BN-101 | 2024-09-01 | NULL |
| 8 | 106 | WK-202 | 2024-03-03 | NULL |
Output
| cohort_month | renters | retained | retention_pct |
|---|---|---|---|
| 2024-01 | 3 | 2 | 66.7 |
| 2024-02 | 2 | 1 | 50 |
| 2024-03 | 1 | 1 | 100 |
How to solve Six-Month Renter Retention by Move-In Cohort
Cohort analysis is two steps: put each renter in a cohort, then measure an outcome per renter and aggregate. The first CTE takes MIN(start_date) per renter — that date fixes the cohort (DATE_FORMAT(…, '%Y-%m')) and the checkpoint, DATE_ADD(first_start, INTERVAL 6 MONTH). The outcome is an EXISTS: is there a lease of this renter, any flat, that started on or before the checkpoint and ends on or after it or is still open?
Looking at all leases is what makes movers count: renter 101's first lease ended on 9 July, but the new lease starting 10 July covers the checkpoint of 10 July. Renter 103's lease ends on 5 July — the very checkpoint, inclusive — so 103 is retained too, while 102 left in June. Renter 105 left after three months and came back in September; on the checkpoint, 14 August, there was no lease, so 105 is not retained.
The alternative LEFT JOINs the covering leases and counts distinct renters on each side. Each cohort's percentage has a non-zero denominator, since a cohort exists only because it has renters.
Reference solution (MySQL)
WITH firsts AS (
SELECT renter_id, MIN(start_date) AS first_start
FROM RenterLease
GROUP BY renter_id
), flagged AS (
SELECT f.renter_id,
DATE_FORMAT(f.first_start, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM RenterLease l
WHERE l.renter_id = f.renter_id
AND l.start_date <= DATE_ADD(f.first_start, INTERVAL 6 MONTH)
AND (l.end_date IS NULL OR l.end_date >= DATE_ADD(f.first_start, INTERVAL 6 MONTH))
) THEN 1 ELSE 0 END AS retained
FROM firsts f
)
SELECT cohort_month,
COUNT(*) AS renters,
SUM(retained) AS retained,
ROUND(100 * SUM(retained) / COUNT(*), 1) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
SELECT LEFT(f.first_start, 7) AS cohort_month,
COUNT(DISTINCT f.renter_id) AS renters,
COUNT(DISTINCT l.renter_id) AS retained,
ROUND(100 * COUNT(DISTINCT l.renter_id) / COUNT(DISTINCT f.renter_id), 1) AS retention_pct
FROM (SELECT renter_id, MIN(start_date) AS first_start FROM RenterLease GROUP BY renter_id) f
LEFT JOIN RenterLease l
ON l.renter_id = f.renter_id
AND l.start_date <= DATE_ADD(f.first_start, INTERVAL 6 MONTH)
AND COALESCE(l.end_date, '9999-12-31') >= DATE_ADD(f.first_start, INTERVAL 6 MONTH)
GROUP BY LEFT(f.first_start, 7)
ORDER BY cohort_month← Top Two Agents per City by Commission, Ties Included · Store SKUs At or Below Their Reorder Point →