We use analytics and advertising cookies to understand how the site is used and whether our ads on Facebook and Instagram work. They are set only if you accept. See our Privacy Policy for details.
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.
A medium sql problem, graded against 6 test cases (4 of them hidden).
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 SQL problems →```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.
Read off this problem's own test suite, so these are the cases a submission actually has to survive.
These apply to the pattern as a whole, not just this problem.