The finance team wants to identify paid orders whose value is higher than the typical paid order.
Using the orders table, return orders with status = 'paid' whose amount_usd is strictly greater than the average amount of paid orders. Orders of any other status, including missing (NULL) statuses, must not contribute to the average or appear in the result.
Ordering: Sort by order_id in ascending order.
Returnorder_idamount_usd
About this problem
You practise comparing each row with an aggregate of a filtered set. From orders, you find paid orders whose amount_usd is strictly above the average of paid orders only. That means computing a benchmark in a subquery or CTE, then filtering rows against it, with the same status = 'paid' condition applied in both places. It’s a typical finance question: which sales were larger than a normal sale? The answer is only meaningful when the benchmark and the rows being judged come from the same population.
The trap is computing the benchmark with a bare SELECT AVG(amount_usd) FROM orders. That average includes refunded, cancelled and delivered orders, plus any with a NULL status, so it describes all orders, not paid ones. In the sample, the non-paid orders include a small refund and a mid-sized delivered order, which pull the average away from the paid figure and can move an order to the wrong side of the line. The query still returns plausible rows, so the mistake is easy to miss. Be careful with status <> 'refunded'-style exclusions too: they let other statuses in and, because comparisons with NULL are unknown, they quietly drop NULL rows in a way you didn’t choose. Finally, the prompt says “strictly greater”, so >= would also keep an order that exactly equals the average.
Read the lesson: Subqueries & CTEs (P10) →