TryBench
Data Analysis & SQL · Practitioner lesson 11 of 19

Counting Unique Records

Count unique individuals instead of duplicate transactions in summary reports.

Why this matters

Counting orders is not the same as counting people. If a loyal buyer orders five times, they should count once as a customer.

Orders Are Not People

COUNT(order_id) counts purchases, so repeat buyers make the number too big. COUNT(DISTINCT customer_id) counts each person once.

In real life: A market trader sold to 100 people, but one man bought 5 times. 100 orders is not 100 customers. DISTINCT counts that man once.

Name Your Calculated Columns

A raw total gets an ugly default name. Give it a clear one, like AS active_customers.

In real life: COUNT(DISTINCT customer_id) counts each person once. Use it when the question is about people.

Group by Every Plain Column

Every column in SELECT that is not inside COUNT or SUM must also appear in GROUP BY, like GROUP BY department.

In real life: If you ask for "department" and "how many customers", SQL needs to know how to split the table. GROUP BY department tells it to make one bucket for each department.

Your practice task

Calculate the count of unique, distinct customers who purchased from each department across all recorded orders. Avoid inflating numbers when repeat buyers purchase multiple times in the same department, and name the count active_customers.

How to do it

  1. Decide what you are counting: orders or people.
  2. Make sure a repeat buyer counts only once.
  3. Show one result row for each department.
  4. Press Run proof and read the checklist.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Unique Customer Count

Prevents repeat buyers from distorting reach and active user metrics.

SELECT department, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY department;

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL