How do you approach SQL: Orders Above Customer's Own Average (Correlated Subquery)?

Correlated subquery: the inner SELECT references the outer row's customer_id.

What are common mistakes on SQL: Orders Above Customer's Own Average (Correlated Subquery)?

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: Orders Above Customer's Own Average (Correlated Subquery)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `Orders(id, customer_id, total)`, return orders where `total > (the average total of that same customer's orders)`. Sort by id ascending. **For the auto-grader:** return the matching order objects.

Example 1

Input: orders = [{id:1,customer_id:1,total:50},{id:2,customer_id:1,total:200},{id:3,customer_id:2,total:80},{id:4,customer_id:2,total:80}]

Output: [{id:2,customer_id:1,total:200}]

Explanation: Customer 1 avg = 125; order 2 (200) is above. Customer 2 avg = 80; nobody is strictly above.

Constraints

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

SQL: Orders Above Customer's Own Average (Correlated Subquery): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 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

Correlated subquery: the inner SELECT references the outer row's customer_id.
Faster alternative: window function AVG(total) OVER (PARTITION BY customer_id) computes per-customer avg in one pass.

The optimal approach

Show the approach: Canonical SQL (correlated subquery):

```sql

SELECT o.id, o.customer_id, o.total

FROM Orders o

WHERE o.total > (

SELECT AVG(o2.total) FROM Orders o2 WHERE o2.customer_id = o.customer_id

)

ORDER BY o.id;

```

Window function rewrite (often faster):

```sql

SELECT id, customer_id, total

FROM (

SELECT id, customer_id, total,

AVG(total) OVER (PARTITION BY customer_id) AS cust_avg

FROM Orders

) t

WHERE total > cust_avg

ORDER BY id;

```

Why it matters: the correlated subquery may execute once per outer row (O(n*m)) on naive engines. The window-function version sorts/partitions once. On modern Postgres / SQL Server the optimizer often rewrites the correlated form, but in MySQL it may not - measure before assuming.

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.

← Back to problems

SQL: Orders Above Customer's Own Average (Correlated Subquery)

MediumDatabases

Description

Example 1

Input: orders = [{id:1,customer_id:1,total:50},{id:2,customer_id:1,total:200},{id:3,customer_id:2,total:80},{id:4,customer_id:2,total:80}]

Output: [{id:2,customer_id:1,total:200}]

Explanation: Customer 1 avg = 125; order 2 (200) is above. Customer 2 avg = 80; nobody is strictly above.

Constraints

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

SQL: Orders Above Customer's Own Average (Correlated Subquery): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 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

Correlated subquery: the inner SELECT references the outer row's customer_id.
Faster alternative: window function AVG(total) OVER (PARTITION BY customer_id) computes per-customer avg in one pass.

The optimal approach

Show the approach: Canonical SQL (correlated subquery):

```sql

SELECT o.id, o.customer_id, o.total

FROM Orders o

WHERE o.total > (

SELECT AVG(o2.total) FROM Orders o2 WHERE o2.customer_id = o.customer_id

)

ORDER BY o.id;

```

Window function rewrite (often faster):

```sql

SELECT id, customer_id, total

FROM (

SELECT id, customer_id, total,

AVG(total) OVER (PARTITION BY customer_id) AS cust_avg

FROM Orders

) t

WHERE total > cust_avg

ORDER BY id;

```

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.