Problem
Task
Return orders whose total amount is greater than the average order amount.
Schema
Table Schema
orders(id, customer_id, total_amount, ordered_at)Input
Sample Data
| id | customer_id | total_amount | ordered_at |
|---|---|---|---|
| 101 | 1 | 50 | 2026-03-02 |
| 102 | 2 | 150 | 2026-03-06 |
| 103 | 3 | 100 | 2026-03-09 |
Output
Expected Output
| id | customer_id | total_amount | ordered_at |
|---|---|---|---|
| 102 | 2 | 150 | 2026-03-06 |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT *
FROM orders
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders
);Explanation
The subquery calculates the average total_amount across all orders. The outer query then keeps only rows with a total_amount greater than that value.
Common Mistakes
- Trying to use AVG(total_amount) directly in WHERE without a subquery.
- Using >= when the problem asks for greater than the average.
- Calculating the average per customer instead of across all orders.
Concepts
Related Concepts
Subquery
AVG
Scalar Subqueries
Comparison
Next practice