Practice · medium · dataset: ecom
Orders above the customer's average
For each customer, find their orders whose value is above that customer’s own average order value.
Order value is the sum of quantity times line unit_price for that order.
Return order_id, customer_id, and order_total rounded to two decimals. Sort by customer_id
ascending, then by order_id ascending.
Tables available
| Table | Columns |
|---|---|
orders |
order_id, customer_id, order_date, status, channel |
order_items |
order_id, product_id, quantity, unit_price |
An order exactly equal to the customer’s average is not above it and must be excluded. Customers with a single order therefore contribute no rows.
⌘/Ctrl + Enter to grade
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.