How do you approach SQL: Order Count Per Customer (GROUP BY)?

GROUP BY customer_id, then COUNT(*).

What are common mistakes on SQL: Order Count Per Customer (GROUP BY)?

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.

Sitelet https://gitgood.dev/coding/sql-group-by-count-orders

gitGood.dev

← Back to problems

SQL: Order Count Per Customer (GROUP BY)

EasyDatabases

Description

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

Given Orders(id, customer_id, total), return each customer_id with their order count. Sort by customer_id ascending. Customers with no orders are not in the input and not in the output.

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

Example 1

Input: orders = [{id:1,customer_id:1,total:10},{id:2,customer_id:1,total:20},{id:3,customer_id:2,total:5}]

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

Constraints

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

SQL: Order Count Per Customer (GROUP BY): 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, then COUNT(*).
Use a Map keyed by customer_id in the simulation.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT customer_id, COUNT(*) AS order_count

FROM Orders

GROUP BY customer_id

ORDER BY customer_id;

```

`COUNT(*)` counts rows including NULLs in any column. `COUNT(col)` would skip rows where `col` is NULL. `COUNT(DISTINCT col)` counts unique non-null values. These distinctions trip up candidates regularly.

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.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

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.