Next-Month Retention of First-Order Cohorts

Hard
167.

Next-Month Retention of First-Order Cohorts

A customer's cohort is the calendar month of their first order. A customer is retained if they ordered again at any time in the calendar month right after their cohort month (a January customer must order in February; ordering again in January, or only in March, does not count).

Return one row per cohort with cohort_month (YYYY-MM), customers (the cohort's size), retained and retention_pct (retained as a percentage of customers, rounded to 1 decimal place). Order the rows by cohort_month.

Tables

Table: Orders

ColumnType
order_idint
customer_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per delivered order.

Example

The rows must come back in the order shown.

Orders

order_idcustomer_idplaced_atorder_value
1712024-01-04 13:00:00300
2712024-02-14 20:00:00420
3722024-01-20 19:30:00250
4722024-01-28 21:00:00310
5732024-01-31 23:00:00199
6732024-03-02 12:00:00260
7742024-12-30 20:00:00500
8742025-01-05 13:30:00350
9752024-12-12 19:00:00280
10762024-02-29 22:00:00330
1 more row

Output

cohort_monthcustomersretainedretention_pct
2024-013133.3
2024-0211100
2024-122150
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.

Next-Month Retention of First-Order Cohorts — SQL Problem

A customer's cohort is the calendar month of their first order.

  • Difficulty: Hard
  • Topics: Dates, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #167

Problem statement

A customer's cohort is the calendar month of their first order. A customer is retained if they ordered again at any time in the calendar month right after their cohort month (a January customer must order in February; ordering again in January, or only in March, does not count).

Return one row per cohort with cohort_month (YYYY-MM), customers (the cohort's size), retained and retention_pct (retained as a percentage of customers, rounded to 1 decimal place). Order the rows by cohort_month.

Tables

Table: Orders

ColumnType
order_idint
customer_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per delivered order.

Examples

Example 1

Orders

order_idcustomer_idplaced_atorder_value
1712024-01-04 13:00:00300
2712024-02-14 20:00:00420
3722024-01-20 19:30:00250
4722024-01-28 21:00:00310
5732024-01-31 23:00:00199
6732024-03-02 12:00:00260
7742024-12-30 20:00:00500
8742025-01-05 13:30:00350
9752024-12-12 19:00:00280
10762024-02-29 22:00:00330
11762024-03-01 09:00:00150

Output

cohort_monthcustomersretainedretention_pct
2024-013133.3
2024-0211100
2024-122150

How to solve Next-Month Retention of First-Order Cohorts

A cohort analysis is a chain of small steps, which is what CTEs are for. First find each customer's first order with MIN(placed_at) and reduce it to the first day of its month (DATE_FORMAT(…, '%Y-%m-01')). That date is the cohort.

Next, flag each customer: retained when an order exists in the half-open window [cohort + 1 month, cohort + 2 months). DATE_ADD(…, INTERVAL n MONTH) does the calendar arithmetic, including December → January of the next year (customer 74) and February's short month (customer 76, first order 29 February, retained by 1 March). A repeat order in the same month (customer 72) or a skip to the month after next (customer 73) is not in the window. EXISTS makes each customer count once however many orders they placed.

Finally group the flags by cohort: COUNT(*) is the size, SUM(kept) the retained, and their ratio × 100, rounded to one decimal, the percentage. Reducing every order to its distinct YYYY-MM and joining a cohort to the month exactly one later (TIMESTAMPDIFF(MONTH, …) = 1 between the first days) turns the flag into a LEFT JOIN instead. Each customer is looked up once with an index on (customer_id, placed_at).

Reference solution (MySQL)

WITH firsts AS (
  SELECT customer_id, DATE_FORMAT(MIN(placed_at), '%Y-%m-01') AS cohort_start
  FROM Orders
  GROUP BY customer_id
), flagged AS (
  SELECT f.customer_id, f.cohort_start,
         CASE WHEN EXISTS (
           SELECT 1 FROM Orders o
           WHERE o.customer_id = f.customer_id
             AND o.placed_at >= DATE_ADD(f.cohort_start, INTERVAL 1 MONTH)
             AND o.placed_at < DATE_ADD(f.cohort_start, INTERVAL 2 MONTH)
         ) THEN 1 ELSE 0 END AS kept
  FROM firsts f
)
SELECT LEFT(cohort_start, 7) AS cohort_month,
       COUNT(*) AS customers,
       SUM(kept) AS retained,
       ROUND(100 * SUM(kept) / COUNT(*), 1) AS retention_pct
FROM flagged
GROUP BY LEFT(cohort_start, 7)
ORDER BY cohort_month

Another way

WITH months AS (
  SELECT DISTINCT customer_id, DATE_FORMAT(placed_at, '%Y-%m') AS ym FROM Orders
), cohort AS (
  SELECT customer_id, MIN(ym) AS ym FROM months GROUP BY customer_id
)
SELECT c.ym AS cohort_month, COUNT(*) AS customers, COUNT(n.customer_id) AS retained,
       ROUND(COUNT(n.customer_id) * 100 / COUNT(*), 1) AS retention_pct
FROM cohort c
LEFT JOIN months n ON n.customer_id = c.customer_id
  AND TIMESTAMPDIFF(MONTH, CONCAT(c.ym, '-01'), CONCAT(n.ym, '-01')) = 1
GROUP BY c.ym
ORDER BY cohort_month

Another way

WITH firsts AS (SELECT customer_id, MIN(placed_at) AS first_at FROM Orders GROUP BY customer_id)
SELECT DATE_FORMAT(f.first_at, '%Y-%m') AS cohort_month, COUNT(*) AS customers,
       SUM(CASE WHEN (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = f.customer_id
                       AND DATE_FORMAT(o.placed_at, '%Y-%m') = DATE_FORMAT(DATE_ADD(DATE_FORMAT(f.first_at, '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m')) > 0
                THEN 1 ELSE 0 END) AS retained,
       ROUND(100 * SUM(CASE WHEN (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = f.customer_id
                       AND DATE_FORMAT(o.placed_at, '%Y-%m') = DATE_FORMAT(DATE_ADD(DATE_FORMAT(f.first_at, '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m')) > 0
                THEN 1 ELSE 0 END) / COUNT(*), 1) AS retention_pct
FROM firsts f
GROUP BY DATE_FORMAT(f.first_at, '%Y-%m')
ORDER BY cohort_month

← Longest On-Time Delivery Streak of Each Rider · Double-Logged Rider Shifts That Overlap →