Retention and Cohort Tables

A meal-kit service signed up 4 customers in its first week. In the third week after that first week, 3 of the 4 received a box, so 3 / 4 = 75 percent of the group was active. The service signed up 4 more customers in its second week, and in the third week after that week none of them received a box, which is 0 / 4 = 0 percent. The two figures are comparable because each one is measured the same number of weeks after its group started.

Each figure describes a group of customers who started in the same period (a cohort), and the share of that group still active N periods later (its retention for period N). A table with one cohort on each row and one period on each column (a cohort table) shows all of these figures at once.

The Tables

subscribers has one row per customer with the signup day, and deliveries has one row per box with its delivery_id, subscriber_id, and day_no. Days are integer day numbers, and the data ends on day 35. To save space, the table below lists each subscriber's delivery days; deliveries itself has 35 rows.

Two rows need attention. Subscriber 116 signed up on day 12 and never received a box, and subscriber 104 received two boxes in the same week, on days 33 and 35. Every query below was run on MySQL 8.4, and the output shown is the real output.

Cohorts From Day Numbers

A signup cohort is the set of subscribers who signed up in the same period, here the same week. Week 1 is days 1 to 7 and week 2 is days 8 to 14, so the week of a day is FLOOR((day_no - 1) / 7) + 1. Subtracting 1 first matters, because FLOOR(day_no / 7) would put day 7 with days 8 to 13.

SELECT FLOOR((signup_day - 1) / 7) + 1 AS cohort_week,
       COUNT(*) AS subscribers,
       MIN(signup_day) AS first_signup_day,
       MAX(signup_day) AS last_signup_day
FROM subscribers
GROUP BY cohort_week
ORDER BY cohort_week;
Copy

Output:

+-------------+-------------+------------------+-----------------+
| cohort_week | subscribers | first_signup_day | last_signup_day |
+-------------+-------------+------------------+-----------------+
|           1 |           4 |                2 |               7 |
|           2 |           4 |                9 |              13 |
|           3 |           4 |               15 |              21 |
|           4 |           2 |               23 |              27 |
|           5 |           2 |               31 |              33 |
+-------------+-------------+------------------+-----------------+
Copy

The sizes come from subscribers, not from deliveries, for a reason given in the section on denominators.

Week-N Retention

Week-N retention is the share of a cohort that was active in the Nth week after its signup week, where week 0 is the signup week itself. Here, active means at least one delivery. Cohort 1's week 3 is calendar week 4, when subscribers 101, 102, and 104 received boxes: 3 of 4, or 75 percent.

The query reduces the deliveries to one row per subscriber and active week, then counts subscribers per cohort and week number, with a CTE for each step as in CTEs and Recursive CTEs.

WITH cohorts AS (
  SELECT subscriber_id, FLOOR((signup_day - 1) / 7) + 1 AS cohort_week
  FROM subscribers
),
activity AS (
  -- one row per subscriber and week with at least one box
  SELECT DISTINCT subscriber_id, FLOOR((day_no - 1) / 7) + 1 AS active_week
  FROM deliveries
)
SELECT c.cohort_week,
       a.active_week - c.cohort_week AS week_n,
       COUNT(*) AS active_subscribers
FROM cohorts c
JOIN activity a ON a.subscriber_id = c.subscriber_id
GROUP BY c.cohort_week, week_n
ORDER BY c.cohort_week, week_n;
Copy

Output:

+-------------+--------+--------------------+
| cohort_week | week_n | active_subscribers |
+-------------+--------+--------------------+
|           1 |      0 |                  4 |
|           1 |      1 |                  4 |
|           1 |      2 |                  2 |
|           1 |      3 |                  3 |
|           1 |      4 |                  2 |
|           2 |      0 |                  3 |
|           2 |      1 |                  1 |
|           2 |      2 |                  2 |
|           3 |      0 |                  4 |
|           3 |      1 |                  2 |
|           3 |      2 |                  2 |
|           4 |      0 |                  2 |
|           4 |      1 |                  1 |
|           5 |      0 |                  2 |
+-------------+--------+--------------------+
Copy

Without the DISTINCT in activity, subscriber 104's two boxes in week 5 would make cohort 1, week 4 read 3 instead of 2. Cohort 2 has no row for week 3, because nobody in it received a box in week 5, and GROUP BY cannot return a group with no rows.

The Cohort Table

Conditional aggregation computes several aggregates in one GROUP BY, where each aggregate reads only the rows that match its own CASE condition. Snowflake and BigQuery document a PIVOT operator that turns rows into columns. MySQL documents no PIVOT and no FILTER clause on aggregates, so each column below is a SUM over a CASE expression.

WITH cohorts AS (
  SELECT subscriber_id, FLOOR((signup_day - 1) / 7) + 1 AS cohort_week
  FROM subscribers
),
activity AS (
  -- one row per subscriber and week with at least one box
  SELECT DISTINCT subscriber_id, FLOOR((day_no - 1) / 7) + 1 AS active_week
  FROM deliveries
)
SELECT c.cohort_week,
       COUNT(DISTINCT c.subscriber_id) AS cohort_size,
       SUM(CASE WHEN a.active_week - c.cohort_week = 0 THEN 1 ELSE 0 END) AS wk0,
       SUM(CASE WHEN a.active_week - c.cohort_week = 1 THEN 1 ELSE 0 END) AS wk1,
       SUM(CASE WHEN a.active_week - c.cohort_week = 2 THEN 1 ELSE 0 END) AS wk2,
       SUM(CASE WHEN a.active_week - c.cohort_week = 3 THEN 1 ELSE 0 END) AS wk3,
       SUM(CASE WHEN a.active_week - c.cohort_week = 4 THEN 1 ELSE 0 END) AS wk4
FROM cohorts c
LEFT JOIN activity a ON a.subscriber_id = c.subscriber_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week;
Copy

Output:

+-------------+-------------+------+------+------+------+------+
| cohort_week | cohort_size | wk0  | wk1  | wk2  | wk3  | wk4  |
+-------------+-------------+------+------+------+------+------+
|           1 |           4 |    4 |    4 |    2 |    3 |    2 |
|           2 |           4 |    3 |    1 |    2 |    0 |    0 |
|           3 |           4 |    4 |    2 |    2 |    0 |    0 |
|           4 |           2 |    2 |    1 |    0 |    0 |    0 |
|           5 |           2 |    2 |    0 |    0 |    0 |    0 |
+-------------+-------------+------+------+------+------+------+
Copy

The LEFT JOIN keeps subscriber 116, who has no activity rows, so cohort 2 still has 4 subscribers. Each SUM adds 1 for rows whose week number matches its column and 0 for the rest, so cohort 2 now shows the missing week 3 as 0.