Six-Month Renter Retention by Move-In Cohort

Hard
372.

Six-Month Renter Retention by Move-In Cohort

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

ColumnType
lease_idint
renter_idint
flat_novarchar
start_datedate
end_datedate

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.

Example

The rows must come back in the order shown.

RenterLease

lease_idrenter_idflat_nostart_dateend_date
1101BN-1012024-01-102024-07-09
2101WK-2012024-07-10NULL
3102KH-3052024-01-202024-06-30
4103AU-1102024-01-052024-07-05
5104BN-1022024-02-012025-01-31
6105HN-4042024-02-142024-05-13
7105BN-1012024-09-01NULL
8106WK-2022024-03-03NULL

Output

cohort_monthrentersretainedretention_pct
2024-013266.7
2024-022150
2024-0311100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
lease_idint
renter_idint
flat_novarchar
start_datedate
end_datedate

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_idrenter_idflat_nostart_dateend_date
1101BN-1012024-01-102024-07-09
2101WK-2012024-07-10NULL
3102KH-3052024-01-202024-06-30
4103AU-1102024-01-052024-07-05
5104BN-1022024-02-012025-01-31
6105HN-4042024-02-142024-05-13
7105BN-1012024-09-01NULL
8106WK-2022024-03-03NULL

Output

cohort_monthrentersretainedretention_pct
2024-013266.7
2024-022150
2024-0311100

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_month

Another 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 →