How do you approach SQL: Distinct Product Count Per Customer (COUNT DISTINCT)?

GROUP BY customer_id, COUNT(DISTINCT product_id).

What are common mistakes on SQL: Distinct Product Count Per Customer (COUNT DISTINCT)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Distinct Product Count Per Customer (COUNT DISTINCT)

EasyDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution.

Given OrderItems(order_id, customer_id, product_id, qty), return how many distinct products each customer has bought. Sort by customer_id ascending.

For the auto-grader: return [{ customer_id, distinct_products }].

Example 1

Input: rows = [{order_id:1,customer_id:1,product_id:10,qty:1},{order_id:2,customer_id:1,product_id:10,qty:5},{order_id:3,customer_id:1,product_id:11,qty:1},{order_id:4,customer_id:2,product_id:10,qty:1}]

Output: [{customer_id:1,distinct_products:2},{customer_id:2,distinct_products:1}]

Constraints

- 0 <= rows.length <= 10^4

SQL: Distinct Product Count Per Customer (COUNT DISTINCT): approach, complexity and pitfalls

An easy sql problem, graded against 6 test cases (4 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

GROUP BY customer_id, COUNT(DISTINCT product_id).
DISTINCT inside COUNT is the key - COUNT(product_id) would count rows, not unique products.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT customer_id, COUNT(DISTINCT product_id) AS distinct_products

FROM OrderItems

GROUP BY customer_id

ORDER BY customer_id;

```

`COUNT(DISTINCT col)` is significantly more expensive than `COUNT(col)` on most engines because it must hash or sort to dedupe. For very large groups, an approximate variant (Postgres `COUNT(DISTINCT col)` vs `approx_count_distinct` in some dialects, BigQuery's `APPROX_COUNT_DISTINCT`) trades accuracy for speed.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.