Level 8 · Debug: one order per customer on every channel?

A ratio that comes out at exactly 1.00 on every row is a strong sign that the top and bottom of the fraction are the same number. That is the case here.

COUNT(column) is not COUNT(DISTINCT column)

COUNT(customer_id) counts the rows where customer_id is not NULL. In orders it is never NULL, so on every channel it counts orders, exactly like COUNT(*). The question is how many different customers there are, and that needs DISTINCT:

SELECT
  channel,
  COUNT(*) AS orders,
  COUNT(DISTINCT customer_id) AS customers
FROM orders
WHERE status = 'completed'
GROUP BY channel;

Web: 5,367 orders from 1,379 customers. App: 3,885 orders from 1,316 customers. Marketplace: 1,597 orders from 784 customers. Dividing gives 3.89, 2.95 and 2.04 orders per customer. The * 1.0 keeps the division decimal in every database.

How to spot it

• Two columns that should differ are identical on every row.
• A ratio is a suspiciously round constant, like 1.00 or 100%.
• The result contradicts something you know about the business, here that most customers order more than once.
• A “customers” number is larger than you would expect the customer base to be. On web it was 5,367, while the whole store has about two thousand customers.

Common mistakes

• Summing the per-channel customer counts to get a total. A customer who used both web and app is counted on both rows, so the three counts add up to 3,479 even though only 1,730 different customers have a completed order. Count distinct customers without the GROUP BY for the true total.
• Writing DISTINCT COUNT(customer_id). That applies DISTINCT to the finished count, not to the customer ids, and changes nothing.
• Fixing the customers column but not the ratio. The ratio repeats the count, so it needs the same COUNT(DISTINCT customer_id) in its denominator, or it stays at 1.00 while the column next to it looks right.

In SQL Server

COUNT(DISTINCT ...) works the same. The * 1.0 matters more there: COUNT(*) / COUNT(DISTINCT customer_id) divides two integers and SQL Server truncates the result, so 3.89 would show as 3.