How do you approach SQL: Big Spenders (GROUP BY + HAVING)?

WHERE filters rows BEFORE grouping. HAVING filters groups AFTER aggregation.

What are common mistakes on SQL: Big Spenders (GROUP BY + HAVING)?

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: Big Spenders (GROUP BY + HAVING)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `Orders(id, customer_id, total)`, return customer_ids whose **total spend exceeds 1000**. Sort by total spend descending, then customer_id ascending as a tiebreaker. **For the auto-grader:** return `[{ customer_id, total_spend }]`.

Example 1

Input: orders = [{id:1,customer_id:1,total:600},{id:2,customer_id:1,total:500},{id:3,customer_id:2,total:200},{id:4,customer_id:3,total:1500}]

Output: [{customer_id:3,total_spend:1500},{customer_id:1,total_spend:1100}]

Constraints

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

SQL: Big Spenders (GROUP BY + HAVING): approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 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.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

WHERE filters rows BEFORE grouping. HAVING filters groups AFTER aggregation.
You can't use SUM(total) > 1000 in WHERE - it's an aggregate; use HAVING.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT customer_id, SUM(total) AS total_spend

FROM Orders

GROUP BY customer_id

HAVING SUM(total) > 1000

ORDER BY total_spend DESC, customer_id ASC;

```

WHERE vs HAVING - the classic interview question:

- `WHERE` runs before `GROUP BY`. It filters individual rows, can't reference aggregates.

- `HAVING` runs after `GROUP BY`. It filters groups, must reference aggregates or grouped columns.

Mixing them is fine and often correct: `WHERE order_date >= '2026-01-01' GROUP BY ... HAVING SUM(total) > 1000`.

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.