Repeat-Guest Retention Within 90 Days by Cohort
A hotel booking app groups new guests into cohorts by the month of their first booking.
- Difficulty: Hard
- Topics: Subqueries, Dates, Aggregation
- Dialect: MySQL
- Problem: #543
Problem statement
A hotel booking app groups new guests into cohorts by the month of their first booking. A guest is retained if they made another booking 1 to 90 days after their first booking date (a second booking on the same day as the first does not count).
For every cohort return cohort_month (YYYY-MM), new_guests, retained_guests and retention_pct = 100 × retained / new, rounded to 2 decimals. Order by cohort_month.
Tables
Table: AppBooking
| Column | Type |
|---|---|
| booking_id | int |
| guest_id | int |
| booked_on | date |
| booking_value | int |
Primary key: booking_id.
One row per booking made in the app; a guest can book several times, including twice on one day.
Examples
Example 1
AppBooking
| booking_id | guest_id | booked_on | booking_value |
|---|---|---|---|
| 1 | 11 | 2024-01-05 | 4200 |
| 2 | 11 | 2024-03-20 | 6100 |
| 3 | 12 | 2024-01-18 | 3800 |
| 4 | 12 | 2024-01-18 | 2900 |
| 5 | 13 | 2024-01-25 | 5400 |
| 6 | 13 | 2024-04-24 | 4700 |
| 7 | 14 | 2024-02-02 | 7300 |
| 8 | 14 | 2024-05-02 | 3100 |
| 9 | 15 | 2024-02-14 | 8800 |
Output
| cohort_month | new_guests | retained_guests | retention_pct |
|---|---|---|---|
| 2024-01 | 3 | 2 | 66.67 |
| 2024-02 | 2 | 1 | 50 |
How to solve Repeat-Guest Retention Within 90 Days by Cohort
Retention analysis always starts by reducing the events to one row per user carrying the anchor date — here MIN(booked_on) per guest. The cohort is the month of that date, DATE_FORMAT(first_on, '%Y-%m').
Next, each guest gets a 0/1 retained flag: does any booking fall strictly after the first date and at most 90 days after it? An EXISTS over booked_on > first_on AND booked_on <= DATE_ADD(first_on, INTERVAL 90 DAY) says exactly that. The strict > handles the same-day second booking (a guest who booked twice on day one is not retained by it), and <= … 90 DAY keeps the 90th day in while the 91st is out.
Only then is the data grouped by cohort: COUNT(*) new guests, SUM(retained) retained ones, and the percentage rounded to two decimals. The alternative LEFT JOINs each guest's first date to the bookings in the 1–90 day window and counts distinct guests on both sides — without DISTINCT, a guest with two return bookings would be counted twice. Both read the bookings twice; an index on (guest_id, booked_on) makes the window lookup cheap.
Reference solution (MySQL)
WITH firsts AS (
SELECT guest_id, MIN(booked_on) AS first_on FROM AppBooking GROUP BY guest_id
), flagged AS (
SELECT f.guest_id, DATE_FORMAT(f.first_on, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (SELECT 1 FROM AppBooking b
WHERE b.guest_id = f.guest_id
AND b.booked_on > f.first_on
AND b.booked_on <= DATE_ADD(f.first_on, INTERVAL 90 DAY)) THEN 1 ELSE 0 END AS retained
FROM firsts f
)
SELECT cohort_month, COUNT(*) AS new_guests, SUM(retained) AS retained_guests,
ROUND(100 * SUM(retained) / COUNT(*), 2) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
SELECT LEFT(f.first_on, 7) AS cohort_month, COUNT(DISTINCT f.guest_id) AS new_guests,
COUNT(DISTINCT b.guest_id) AS retained_guests,
ROUND(100 * COUNT(DISTINCT b.guest_id) / COUNT(DISTINCT f.guest_id), 2) AS retention_pct
FROM (SELECT guest_id, MIN(booked_on) AS first_on FROM AppBooking GROUP BY guest_id) f
LEFT JOIN AppBooking b ON b.guest_id = f.guest_id AND DATEDIFF(b.booked_on, f.first_on) BETWEEN 1 AND 90
GROUP BY LEFT(f.first_on, 7)
ORDER BY cohort_month← Peak In-House Guests per Hotel · Median Departure Delay per Airport →