SQL Repeat Customer Analysis

Count entities with activity on more than one distinct occasion.

What & Why

Repeat-customer analysis separates entities with one qualifying transaction from those with two or more. Repeat behavior is a core loyalty signal and should be measured at customer grain.

See How It Works

BUSINESS QUESTION

Find SaaS accounts with at least two distinct paid invoices.

id	name	plan	mrr	created_at	churned_at	country	employee_count	industry
1	Acme Labs	growth	240.00	2024-01-08 10:00:00+00	NULL	US	45	software
2	Northstar Co	starter	90.00	2024-02-12 09:30:00+00	2024-05-18 12:00:00+00	CA	18	services
3	Atlas Works	scale	480.00	2024-03-04 15:10:00+00	NULL	GB	120	manufacturing
4	Bright Path	growth	180.00	2024-04-19 11:45:00+00	NULL	US	62	education

id	account_id	amount	status	due_date	paid_at
10	1	240.00	paid	2024-02-01	2024-01-29 13:00:00+00
11	1	260.00	paid	2024-03-01	2024-02-28 16:30:00+00
12	2	90.00	overdue	2024-03-15	NULL
13	3	480.00	open	2024-04-01	NULL

EXAMPLE QUERY

SELECT
  a.id AS account_id,
  a.name,
  COUNT(DISTINCT i.id) AS paid_purchases,
  SUM(i.amount) AS paid_value
FROM saas.accounts a
JOIN saas.invoices i ON i.account_id = a.id
WHERE i.status = 'paid'
GROUP BY a.id, a.name
HAVING COUNT(DISTINCT i.id) >= 2
ORDER BY paid_purchases DESC, account_id;

RESULT — account with at least two paid purchases

account_id	name	paid_purchases	paid_value
1	Acme Labs	2	500.00

Acme Labs has two distinct paid invoices totaling 500.00; every other account is removed by HAVING.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice