When a Distinct Count Is Enough

Concepts covered: Distinct Counting, Sums & Averages, Deduplication, Approximate Aggregates, Join Cardinality

The numbers duplicates can move

The prompt rarely contains the word duplicate. It arrives as 'revenue looks high' or 'how many orders did we take on Thursday', and the first thing scored is whether you can say which of those numbers repeated rows are able to move. A distinct count collapses repeats before it counts, so a key that appears 3 times contributes 1. Sums and averages read every row, and so does a plain row count, so each extra copy adds its value again.

-- orders declares PRIMARY KEY (order_id); a batch was loaded twice
SELECT
  COUNT(*)                 AS row_count,
  COUNT(DISTINCT order_id) AS order_count,
  SUM(amount)              AS revenue
FROM orders
WHERE order_date = DATE '2024-03-14';

Read the result back the way you would in the room. The order_count column is already correct. The retried batch added 1,400 rows, each a copy of an order already present, and COUNT(DISTINCT order_id) folds them away. The revenue column is high by exactly the amounts on those 1,400 copies, 70,200.00 here. No rearrangement of the SELECT list repairs it until something decides which rows count.

Aggregates that ignore repeats, and ones that add them

Unchanged by identical copies

• COUNT(DISTINCT key): repeats collapse before counting
• MIN and MAX: a second copy of a value changes neither
• APPROX_COUNT_DISTINCT: the sketch ignores values it has seen
• EXISTS and IN tests: a match is a match at any copy count

Inflated by identical copies

• SUM(amount): every copy adds its amount again
• COUNT(*) and COUNT(col): every copy is a row
• AVG(amount): drifts toward the duplicated orders
• Ratios built on these, such as revenue per order

AVG is the one candidates misjudge. It looks safe because the numerator and the denominator both grow. Only the duplicated orders are counted twice, though, so the average moves toward whatever those orders are worth. If the retried batch held the night's large wholesale orders, AVG(amount) rises even though no order changed at all.

When the copies disagree

All of that assumes identical copies, which is what a retried batch produces. A change-data-capture landing table such as orders_raw holds something else: every version of every order. Order 5000 appears once as 'placed' and again as 'shipped'. COUNT(DISTINCT order_id) still counts orders correctly there, right up until the query filters on a column that changes between versions.

-- orders_raw keeps every version of every order
SELECT COUNT(DISTINCT order_id) AS open_orders
FROM orders_raw
WHERE status = 'placed';

Order 5000 is counted as open, because its 'placed' version passes the WHERE clause, even though the order shipped 5 minutes later. WHERE runs before any aggregate, so the filter picks rows from the whole history before DISTINCT sees them. A distinct count is enough only when every copy of a key agrees on every column the query filters on, and on any column it groups or joins by. Where copies can disagree, reduce to 1 row per key first and filter afterwards.

Deduplicate at the order grain, then sum

SELECT COUNT(*) AS order_count, SUM(amount) AS revenue
FROM (
  SELECT DISTINCT order_id, amount
  FROM orders
  WHERE order_date = DATE '2024-03-14'
) AS one_per_order;

The subquery works because the retried copies agree on every column it selects, so DISTINCT reduces them to 1 row per order. If 2 copies of an order carried different amounts, the subquery would return both and the sum would stay inflated. At that point you need a ranking that keeps 1 chosen version per key.

The answer that loses the point here is SUM(DISTINCT amount). It deduplicates amounts, while the thing that repeated is orders. Any 2 different orders that each cost 49.99 collapse into 1, and the total comes out low. On a day with 46,800 orders and a few hundred price points, it undercounts heavily. Put DISTINCT inside a subquery at the order grain, never inside the SUM.

The sentence that opens the answer

Sort the metrics before touching the data: 'Order count is safe, because it is distinct on the key. Revenue and average order value need 1 row per order first. Let me check whether the copies are identical, because that decides how I collapse them.' Those 3 sentences show the interviewer you know which numbers can move, and the subquery comes next.

About this lesson

This section is part of the Deduplication: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.