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

Loading editor...