SQL First Purchase Analysis

ROW_NUMBER, partitioned by user, filtered to rn = 1 — the same Top-N-per-Group pattern from earlier.

What & Why

First-purchase analysis finds the earliest completed transaction per customer and attributes later analysis to that milestone. It supports time-to-convert, acquisition attribution, and new-customer reporting.

See How It Works

BUSINESS QUESTION

Use each SaaS account's earliest paid invoice as the internal first-purchase milestone.

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,
  MIN(i.paid_at) AS first_purchase_at
FROM saas.accounts a
JOIN saas.invoices i ON i.account_id = a.id
WHERE i.status = 'paid'
  AND i.paid_at IS NOT NULL
GROUP BY a.id, a.name
ORDER BY first_purchase_at, account_id;

RESULT — first paid invoice per account

account_id	name	first_purchase_at
1	Acme Labs	2024-01-29 13:00:00+00

Acme Labs is the only account with paid invoices, and invoice 10 supplies its earliest paid_at timestamp.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice