Monthly Buyer Cohort Retention

Hard
117.

Monthly Buyer Cohort Retention

A customer's cohort is the calendar month of their first order. The retention report asks, for each cohort, how many of its customers ordered again in the next calendar month and how many in the month after that (a customer counts once per column however many orders they placed, and may count in both).

Return cohort_month (YYYY-MM), customers (the cohort's size), month_1 and month_2, ordered by cohort_month. Orders later in the cohort month itself do not count as retention.

Tables

Table: orders

ColumnType
order_idint
customer_idint
order_datedate
order_totalint

Primary key: order_id.

One row per order.

Example

The rows must come back in the order shown.

orders

order_idcustomer_idorder_dateorder_total
112023-12-05899
212024-01-10499
312024-02-021299
422023-12-20650
522023-12-28300
622024-02-15799
732024-01-032499
832024-02-28199
932024-02-29349
1042024-01-301500

Output

cohort_monthcustomersmonth_1month_2
2023-12212
2024-01210
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.

Monthly Buyer Cohort Retention — SQL Dates Problem

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

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

Problem statement

A customer's cohort is the calendar month of their first order. The retention report asks, for each cohort, how many of its customers ordered again in the next calendar month and how many in the month after that (a customer counts once per column however many orders they placed, and may count in both).

Return cohort_month (YYYY-MM), customers (the cohort's size), month_1 and month_2, ordered by cohort_month. Orders later in the cohort month itself do not count as retention.

Tables

Table: orders

ColumnType
order_idint
customer_idint
order_datedate
order_totalint

Primary key: order_id.

One row per order.

Examples

Example 1

orders

order_idcustomer_idorder_dateorder_total
112023-12-05899
212024-01-10499
312024-02-021299
422023-12-20650
522023-12-28300
622024-02-15799
732024-01-032499
832024-02-28199
932024-02-29349
1042024-01-301500

Output

cohort_monthcustomersmonth_1month_2
2023-12212
2024-01210

How to solve Monthly Buyer Cohort Retention

Three steps. First, each customer's first order date — a grouped MIN — which fixes the cohort month. Second, compare every order of the customer with that first date on a month index: YEAR * 12 + MONTH turns calendar months into consecutive integers, so the difference is 1 for the next month even across a year boundary (December 2023 → January 2024). Day-based arithmetic would be wrong here: 30 January to 1 March is only 31 days, yet it is two calendar months on, not one.

Collapse to one row per customer with MAX(CASE …) flags: did they order at index +1, at index +2. Taking MAX rather than SUM is what makes customer 3, with two orders in February, count once. Index 0 (more orders in the cohort month, like customer 2's second December order) sets neither flag. Finally group the customers by cohort: COUNT(*) is the cohort size, the summed flags are the retained counts.

The alternative keeps one row per customer from the start and asks two EXISTS questions per customer, building the target month with DATE_ADD(…, INTERVAL n MONTH). Both do one pass to find cohorts and one look at each customer's orders.

Reference solution (MySQL)

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders
  GROUP BY customer_id
), per_customer AS (
  SELECT f.customer_id, DATE_FORMAT(f.first_date, '%Y-%m') AS cohort_month,
         MAX(CASE WHEN (YEAR(o.order_date) * 12 + MONTH(o.order_date)) - (YEAR(f.first_date) * 12 + MONTH(f.first_date)) = 1 THEN 1 ELSE 0 END) AS m1,
         MAX(CASE WHEN (YEAR(o.order_date) * 12 + MONTH(o.order_date)) - (YEAR(f.first_date) * 12 + MONTH(f.first_date)) = 2 THEN 1 ELSE 0 END) AS m2
  FROM firsts f
  JOIN orders o ON o.customer_id = f.customer_id
  GROUP BY f.customer_id, DATE_FORMAT(f.first_date, '%Y-%m')
)
SELECT cohort_month, COUNT(*) AS customers, SUM(m1) AS month_1, SUM(m2) AS month_2
FROM per_customer
GROUP BY cohort_month
ORDER BY cohort_month

Another way

WITH cohorts AS (
  SELECT customer_id, DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month FROM orders GROUP BY customer_id
)
SELECT c.cohort_month, COUNT(*) AS customers,
       SUM(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
             AND DATE_FORMAT(o.order_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(c.cohort_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')) THEN 1 ELSE 0 END) AS month_1,
       SUM(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
             AND DATE_FORMAT(o.order_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(c.cohort_month, '-01'), INTERVAL 2 MONTH), '%Y-%m')) THEN 1 ELSE 0 END) AS month_2
FROM cohorts c
GROUP BY c.cohort_month
ORDER BY c.cohort_month

← Shopping Sessions Abandoned After Add to Cart · Median Delivery Days per Courier Partner →