Orders vs customers: why weekly customers don't add up to the total

A weekly report shows paid orders and customers on Meridian, a fictional practice marketplace. For the total, someone adds up both columns:

The orders column adds up: there were 9,071 paid orders. The customers column doesn't. Only 3,652 different people paid, so the sum counted everyone who came back in another week once more.

SELECT COUNT(*) AS paid_orders, COUNT(DISTINCT buyer_id) AS customers
FROM meridian.orders
WHERE status = 'paid';
-- result: 9,071 · 3,652

Why orders add up and people don't

Each order belongs to exactly one week. A person can belong to several: 1,823 of the 3,652 paying buyers bought in two or more different weeks, so they sit in two or more rows of the weekly report. Add the rows and each of them is counted again for every extra week.

The shorter the period, the bigger the overcount. Adding up daily customers for the same weeks gives 6,781, almost twice the real number.

The rule:

• Counts of events add up: orders, revenue, refunds, sessions.
• Counts of different things don't: customers, active users, products sold, sellers with a sale.

A total for a distinct count has to be counted again over the whole period. It can't be summed from the parts.

Count each person once, at the level you report

For the whole period, run one COUNT(DISTINCT buyer_id) over the whole period, as in the query above. A BI tool may compute a distinct total row correctly. A spreadsheet SUM under exported weekly numbers will not.

If you need weekly numbers that do add up, count each person in one week only, for example the week of their first paid order:

SELECT first_week, COUNT(*) AS new_customers
FROM (
  SELECT buyer_id, MIN(DATE_TRUNC('week', created_at)) AS first_week
  FROM meridian.orders
  WHERE status = 'paid'
  GROUP BY buyer_id
) AS firsts
GROUP BY first_week
ORDER BY first_week;
-- 1,710 · 1,297 · 494 · 151, which adds up to 3,652

New customers add up because each person has exactly one first week. "Returning customers" in a week are then that week's customers minus its new ones.

Try it on a colleague's question

Try it · 30 seconds · from Atterna’s practice

M

Marta · finance

How many customers bought from us in these four weeks? The weekly dashboard says this:

week from	customers
31 Aug	1,710
7 Sep	1,814
14 Sep	1,987
21 Sep	297

What do you send Marta?

Preparing the practice…

5,808, the four weeks added up1,987, the biggest week3,652: one distinct count over the whole period

Per-customer numbers need a customer denominator

The same money divided by orders and by people gives two true numbers for two different questions:

SELECT
  ROUND(SUM(items_total_eur) / COUNT(*), 2) AS per_order,
  ROUND(SUM(items_total_eur) / COUNT(DISTINCT buyer_id), 2) AS per_customer
FROM meridian.orders
WHERE status = 'paid';
-- result: 60.92 · 151.33

€60.92 is the average order. €151.33 is what an average paying customer spent over these weeks. Swapping one for the other in a forecast, or dividing by COUNT(*) when you meant people, changes the answer by more than two times. 2,431 of the 3,652 paying buyers paid more than once, which is why the gap is this big.

Before counting: who is a customer?

The orders table has 3,705 buyers, and 53 of them only have cancelled orders. Are they customers? For a delivery forecast, maybe. For revenue, no. The period matters too: the practice data ends on 22 September, so "September customers" is 3,587 people in 22 days, not a full month.

None of these choices is wrong. Not saying which one you made is. One question, three right answers shows the same database giving three customer counts, and the metric definition template puts the choices in one line.

Check one number without SQL

If your job is the numbers rather than the SQL, try Wrong Number. Three colleagues ask for a customer count, and one database gives 2,141, 3,670 and 2,339: each is right for one of them. It takes about three minutes, with no code and no account. Play Wrong Number.

FAQ

Why doesn't the sum of weekly unique users equal the monthly total?

Because the same person can be active in several weeks. A weekly unique count includes them in each week, so the sum counts them several times. On the Meridian practice data four weekly customer counts add up to 5,808 while 3,652 different people bought. Count distinct users over the whole month instead.

How do I count unique customers in SQL?

Use COUNT(DISTINCT customer_id) over the rows that match your definition, for example paid orders in the period. Don't use COUNT(*) on an orders table, which counts orders, and don't add up distinct counts from shorter periods or from separate segments.

What is the difference between orders and customers?

An order is an event and a customer is a person who can place many orders. Orders and revenue add up across days, weeks and segments; customers don't. Averages need the matching denominator: revenue per order divides by orders, revenue per customer divides by distinct customers.

Can I add up customers from different segments or channels?

Only if each person belongs to exactly one segment. If someone can be in two (they bought both on mobile and on desktop, or paid one order and cancelled another), the sum counts them twice. Count distinct people over the combined rows instead, or assign each person to one segment by a rule you state.