Cohort analysis in SQL: retention and funnels

A cohort is a group of users who started in the same period, usually the month of their sign-up or first order. Retention is the share of a cohort that is active again in a later period: if 40 customers ordered for the first time in April and 14 of them ordered again in May, the April cohort’s month-1 retention is 35 %. A funnel counts how many users reach each step of a journey in order, such as signing up, a first order and a second order. Cohorts, retention and funnels are all GROUP BY queries over one row per user and period, or per user and step, and in all of them the denominator decides whether a figure is honest: the cohort’s size, only the users old enough to be measured, and only the users who reached the step before.

A cohort table, worked on ten customers

Ten customers of an online shop placed 21 orders between April and July. Each customer’s cohort is the month of the first order, month_number counts the months since then, and the share divides the active customers by the cohort’s size. The query runs on SQLite 3.49.1 in your browser:

-- Ten customers of an online shop and the days on which they ordered.
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  ordered_on  TEXT    NOT NULL   -- 'YYYY-MM-DD' on India's calendar
);
INSERT INTO orders (customer_id, ordered_on) VALUES
  (1, '2026-04-03'), (1, '2026-05-10'), (1, '2026-06-15'), (1, '2026-07-02'),
  (2, '2026-04-07'), (2, '2026-04-20'), (2, '2026-06-01'),
  (3, '2026-04-15'),
  (4, '2026-04-28'), (4, '2026-05-02'),
  (5, '2026-05-05'), (5, '2026-05-25'), (5, '2026-07-19'),
  (6, '2026-05-12'), (6, '2026-06-12'),
  (7, '2026-05-30'),
  (8, '2026-06-02'), (8, '2026-07-08'),
  (9, '2026-06-18'),
  (10, '2026-06-25'), (10, '2026-07-30');

-- A customer's cohort is the month of the first order; month_number counts the months since then.
WITH firsts AS (
  SELECT customer_id, min(ordered_on) AS first_day
  FROM orders
  GROUP BY customer_id
),
activity AS (            -- one row per customer and month in which they ordered
  SELECT DISTINCT o.customer_id,
         strftime('%Y-%m', f.first_day) AS cohort,
         (cast(strftime('%Y', o.ordered_on) AS INTEGER) - cast(strftime('%Y', f.first_day) AS INTEGER)) * 12
           + cast(strftime('%m', o.ordered_on) AS INTEGER)
           - cast(strftime('%m', f.first_day) AS INTEGER) AS month_number
  FROM orders AS o
  JOIN firsts AS f ON f.customer_id = o.customer_id
),
sizes AS (               -- every customer is active in month 0, the month of the first order
  SELECT cohort, count(*) AS cohort_size FROM activity WHERE month_number = 0 GROUP BY cohort
)
SELECT a.cohort, a.month_number, count(*) AS customers, s.cohort_size,
       round(100.0 * count(*) / s.cohort_size, 1) AS retention_pct
FROM activity AS a
JOIN sizes AS s ON s.cohort = a.cohort
GROUP BY a.cohort, a.month_number
ORDER BY a.cohort, a.month_number;

┌─────────┬──────────────┬───────────┬─────────────┬───────────────┐
│ cohort  │ month_number │ customers │ cohort_size │ retention_pct │
├─────────┼──────────────┼───────────┼─────────────┼───────────────┤
│ 2026-04 │ 0            │ 4         │ 4           │ 100.0         │
│ 2026-04 │ 1            │ 2         │ 4           │ 50.0          │
│ 2026-04 │ 2            │ 2         │ 4           │ 50.0          │
│ 2026-04 │ 3            │ 1         │ 4           │ 25.0          │
│ 2026-05 │ 0            │ 3         │ 3           │ 100.0         │
│ 2026-05 │ 1            │ 1         │ 3           │ 33.3          │
│ 2026-05 │ 2            │ 1         │ 3           │ 33.3          │
│ 2026-06 │ 0            │ 3         │ 3           │ 100.0         │
│ 2026-06 │ 1            │ 2         │ 3           │ 66.7          │
└─────────┴──────────────┴───────────┴─────────────┴───────────────┘

Every cohort starts at 100 %, because month 0 is the month in which each of its customers first ordered. Of the four April customers, two ordered in May and two in June, and one, customer 1, ordered in all four months: 50, 50 and 25 %. Later cohorts have fewer months behind them, so the table forms a triangle. Retention does not have to fall every month either: the April cohort holds at 50 % in month 2 because customer 2 came back after a month away.

Three details keep the numbers right. SELECT DISTINCT keeps one row per customer and month, so customer 2’s two April orders count once. The month number comes from years and months, never from days, so an order on the last day of a month and one on the first day of the next are one month apart. And the cohort’s size comes from month 0 of the cohort itself, never from the customers who happen to be active in a later month.

Around such a table sit five more ideas, each in one sentence:

• Day-N retention counts users active exactly N days after their own sign-up, such as day 1, day 7 or day 30.
• Rolling retention counts users active on day N or on any later day.
• Immature cohorts have not yet lived N days, so they leave day N’s denominator.
• A funnel counts users who reached each step after the step before, within a time window.
• Survivorship bias inflates retention when deleted or lapsed accounts drop out of a cohort.

Day 1, day 7 and day 30, with immature cohorts left out Pro: opens with a Pro, Premium or Ultimate pass