Customer cohorts

A cohort is a group of customers who started at the same time. Grouping customers by the month of their first order lets you compare like with like: did January's new customers stick around better than March's?

Each customer's cohort is the month of their earliest order:

SELECT customer_id,
       date_trunc('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id

Count customers per cohort and you have the number of new customers won each month:

WITH cohorts AS (
  SELECT customer_id, date_trunc('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
)
SELECT cohort_month, COUNT(*) AS customers
FROM cohorts
GROUP BY cohort_month

This counts first orders, not sign-ups. Some customers open an account and never buy, and that's a different question.