The retention table

Retention asks: of the customers who started in a month, how many were still ordering one month later, two months later, and so on?

Join each order back to its customer's cohort, and work out how many months after the cohort month it was:

WITH cohorts AS (
  SELECT customer_id, date_trunc('month', MIN(order_date)) AS cohort_month
  FROM orders GROUP BY customer_id
),
activity AS (
  SELECT c.cohort_month,
         date_diff('month', c.cohort_month, date_trunc('month', o.order_date)) AS months_since,
         o.customer_id
  FROM orders o
  JOIN cohorts c ON c.customer_id = o.customer_id
)
SELECT cohort_month, months_since, COUNT(DISTINCT customer_id) AS customers
FROM activity
GROUP BY cohort_month, months_since

Month 0 is the cohort's own month, so it's everyone. Month 1 is how many came back the next month. Laid out as a grid (cohorts down the side, months across the top), this is the retention table every subscription and e-commerce business watches.

Count with COUNT(DISTINCT customer_id): a customer with three orders in a month still counts once.

SELECT date_diff('month', cohort_month, order_month) FROM activity

SELECT DATEDIFF(month, cohort_month, order_month) FROM activity

SELECT TIMESTAMPDIFF(MONTH, cohort_month, order_month) FROM activity

SELECT (EXTRACT(YEAR FROM order_month) - EXTRACT(YEAR FROM cohort_month)) * 12 + EXTRACT(MONTH FROM order_month) - EXTRACT(MONTH FROM cohort_month) FROM activity

SELECT DATE_DIFF(order_month, cohort_month, MONTH) FROM activity