Repeat-Guest Retention Within 90 Days by Cohort

Hard
543.

Repeat-Guest Retention Within 90 Days by Cohort

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

ColumnType
booking_idint
guest_idint
booked_ondate
booking_valueint

Primary key: booking_id.

One row per booking made in the app; a guest can book several times, including twice on one day.

Example

The rows must come back in the order shown.

AppBooking

booking_idguest_idbooked_onbooking_value
1112024-01-054200
2112024-03-206100
3122024-01-183800
4122024-01-182900
5132024-01-255400
6132024-04-244700
7142024-02-027300
8142024-05-023100
9152024-02-148800

Output

cohort_monthnew_guestsretained_guestsretention_pct
2024-013266.67
2024-022150
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.

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

ColumnType
booking_idint
guest_idint
booked_ondate
booking_valueint

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_idguest_idbooked_onbooking_value
1112024-01-054200
2112024-03-206100
3122024-01-183800
4122024-01-182900
5132024-01-255400
6132024-04-244700
7142024-02-027300
8142024-05-023100
9152024-02-148800

Output

cohort_monthnew_guestsretained_guestsretention_pct
2024-013266.67
2024-022150

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_month

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