Counting Distinct Users Who Qualify

Concepts covered: Distinct Counting, Conditional Aggregation, Conditional Logic

The question shifts from a customers table to an events table and the wording barely changes: 'how many users made a purchase, per region'. The events table has one row per event, so a user who bought 3 times has 3 purchase rows. COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) counts purchases, and a candidate who reports that number as the number of buyers has answered a different question. The grain of the input decides which aggregate the question needs, and saying the grain out loud is the first thing the interviewer listens for.

Counting the users rather than the rows puts DISTINCT inside the aggregate and the user id in the THEN: COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END).

The CASE returns the user_id for a purchase row and NULL for every other row. DISTINCT collapses the repeated ids into one each, COUNT skips the NULL, and what remains is the set of users with at least 1 purchase in the group. The run below has 6 events across 2 regions, with user 101 buying twice in North and user 202 buying twice in South, so the 2 counts come apart in both rows.

SELECT region,
  COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) AS purchases,
  COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS buyers
FROM events
GROUP BY region

North holds events 1 to 4: user 101 purchases twice, user 102 views, user 103 purchases. The purchase count is 3; the CASE produced 101 twice, then NULL, then 103, and after DISTINCT that leaves 2 buyers. South holds events 5 and 6, both purchases by user 202, which makes 2 purchases from a single buyer. On a real table that gap is a repeat customer, 3 purchases from 2 buyers, and a dashboard that showed 3 under 'buyers' would overstate the customer base by half.

Which number the question wants

The disguise is in the noun. 'Purchases' and 'orders' and 'events' are rows; 'users', 'customers', 'accounts' and 'sessions' are distinct ids. The interviewer often asks for both in one breath, 'purchases and buyers per region', and wants to see the pair as 2 columns from the same group rather than 1 column with the wrong name. A WHERE event_type = 'purchase' with COUNT(DISTINCT user_id) gets the buyers right but loses the viewers, which is the same shape problem as the opening question: the condition belongs inside each aggregate so that buyers and viewers can sit in one row beside the overall count.

SELECT
  region,
  COUNT(DISTINCT user_id)                                               AS all_users,
  COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)    AS buyers,
  COUNT(DISTINCT CASE WHEN event_type = 'view'     THEN user_id END)    AS viewers
FROM events
GROUP BY region;

Each DISTINCT is computed inside its own aggregate, independently of the others. A user who viewed and then bought is in both buyers and viewers, so those 2 columns can sum to more than all_users. The previous section's reconciliation check does not apply here, and saying so unprompted, 'these are overlapping sets of users, so they won't add up to all_users', is the sentence that separates someone who has built this from someone who has only read the syntax. If the question needs non-overlapping groups of users, label each user once first, by their highest action, and count the labels.

Habits that survive the follow-up

Do

• Say the grain of the input and the grain of the output before writing a line
• Put COUNT(*) or SUM(amount) beside the conditional columns so the slices reconcile
• Write ELSE 0 inside SUM and no ELSE inside COUNT, and say why as you type it
• Add an other bucket, or one labelling CASE, when columns are meant to partition the total
• Reach for COUNT(DISTINCT CASE ...) when the question counts people and the table holds events

Avoid

• Don't open with a WHERE on one category; it answers a third of the question
• Don't write ELSE 0 inside COUNT; every column becomes COUNT(*)
• Don't assume category columns sum to the total; a NULL or an unlisted value lands in none
• Don't report a row count as a user count when a user can appear in many rows
• Don't expect distinct-user columns to add up; one user can qualify for several

About this lesson

This section is part of the Conditional Aggregation: Beginner 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.