What are common mistakes on SQL: Customers With At Least One Order Over $500 (EXISTS)?

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: Customers With At Least One Order Over $500 (EXISTS)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `Customers(id, name)` and `Orders(id, customer_id, total)`, return customer names who have at least one order over $500. Sort alphabetically. The canonical pattern is `WHERE EXISTS (...)` - know why it usually beats `IN (...)` and `JOIN ... DISTINCT`. **For the auto-grader:** implement `customersWithBigOrder(customers, orders)`.

Example 1

Input: customers = [{id:1,name:"Alice"},{id:2,name:"Bob"}], orders = [{id:1,customer_id:1,total:600},{id:2,customer_id:2,total:50}]

Output: ["Alice"]

Constraints

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

SQL: Customers With At Least One Order Over $500 (EXISTS): 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 EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id AND o.total > 500).
EXISTS short-circuits on first match. IN materializes the full set first.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT c.name

FROM Customers c

WHERE EXISTS (

SELECT 1 FROM Orders o

WHERE o.customer_id = c.id

AND o.total > 500

)

ORDER BY c.name;

```

EXISTS vs IN vs JOIN-DISTINCT:

|---|---|---|---|

| `WHERE EXISTS (...)` | yes | yes (stops at first match) | no |

| `JOIN ... DISTINCT` | yes | no | needs DISTINCT to dedupe |

`EXISTS` is usually the safest choice: NULL-safe, semi-join-friendly to the optimizer, no dedupe needed. Modern optimizers often rewrite all three to the same plan, but `NOT IN` with a NULL-containing subquery is a classic correctness bug.

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.