Repeat purchase rate and churn

Two numbers sum up how loyal customers are.

The repeat purchase rate is the share of buying customers who've bought more than once:

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  GROUP BY customer_id
)
SELECT 100.0 * COUNT(CASE WHEN orders >= 2 THEN 1 END) / COUNT(*) AS repeat_rate_pct
FROM per_customer

Churn is the share of customers who were active and then stopped. You have to choose what "active" means. A common way is to compare two periods of the same length:

• active before: ordered between 01/11/2025 and 30/04/2026 (six months)
• churned: one of those, but no order between 01/05/2026 and 31/10/2026 (the next six months)

WITH before AS (
  SELECT DISTINCT customer_id FROM orders
  WHERE order_date >= '2025-11-01' AND order_date < '2026-05-01'
),
after AS (
  SELECT DISTINCT customer_id FROM orders
  WHERE order_date >= '2026-05-01' AND order_date < '2026-11-01'
)
SELECT COUNT(*) AS churned
FROM before b
WHERE NOT EXISTS (SELECT 1 FROM after a WHERE a.customer_id = b.customer_id)

Churn rate is then churned divided by the customers who were active before. Always say which periods you used: "churn" with no definition can't be checked.