Orders Above the Customer's Own Average Basket — SQL Problem
The growth team looks for "splurge" orders: delivered orders whose value is strictly greater than that customer's average delivered order value.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #159
Problem statement
The growth team looks for "splurge" orders: delivered orders whose value is strictly greater than that customer's average delivered order value. Cancelled orders are ignored everywhere — in the average and as candidates.
Return customer_id, order_id, order_value and customer_avg (the customer's average delivered order value, rounded to 2 decimal places). The comparison uses the exact average. Order the rows by customer_id, then order_id.
Tables
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| order_value | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order, value in rupees.
Examples
Example 1
Orders
| order_id | customer_id | placed_at | order_value | status |
|---|---|---|---|---|
| 1 | 51 | 2024-09-01 13:00:00 | 300 | delivered |
| 2 | 51 | 2024-09-03 20:10:00 | 500 | delivered |
| 3 | 51 | 2024-09-05 21:00:00 | 900 | cancelled |
| 4 | 51 | 2024-09-08 13:30:00 | 350 | delivered |
| 5 | 52 | 2024-09-02 19:45:00 | 420 | delivered |
| 6 | 53 | 2024-09-04 12:15:00 | 250 | delivered |
| 7 | 53 | 2024-09-06 12:20:00 | 250 | delivered |
| 8 | 54 | 2024-09-07 20:00:00 | 610 | delivered |
| 9 | 54 | 2024-09-09 20:30:00 | 199 | delivered |
| 10 | 54 | 2024-09-10 21:15:00 | 280 | delivered |
Output
| customer_id | order_id | order_value | customer_avg |
|---|---|---|---|
| 51 | 2 | 500 | 383.33 |
| 54 | 8 | 610 | 363 |
How to solve Orders Above the Customer's Own Average Basket
Every order must be compared with an aggregate of its own customer's orders. The direct way is a correlated subquery: for the outer row o, (SELECT AVG(order_value) FROM Orders a WHERE a.customer_id = o.customer_id AND a.status = 'delivered') is that customer's average, and the WHERE keeps orders strictly above it. The same subquery, rounded, fills the customer_avg column.
The cancelled rule applies twice: a cancelled ₹900 order must not drag the average up, and it must not be a candidate itself. A customer whose delivered orders are all equal (or who has only one) has no order strictly above the average and does not appear.
A derived table with one row per customer (average, sum and count) joined back on customer_id computes each average once instead of once per order; a window AVG(…) OVER (PARTITION BY customer_id) does the same without a join. Testing order_value * n > total keeps the comparison in exact integers — the rounded display value must never decide it. With an index on (customer_id, status) the correlated form is one index range per order.
Reference solution (MySQL)
SELECT o.customer_id, o.order_id, o.order_value,
ROUND((SELECT AVG(a.order_value) FROM Orders a
WHERE a.customer_id = o.customer_id AND a.status = 'delivered'), 2) AS customer_avg
FROM Orders o
WHERE o.status = 'delivered'
AND o.order_value > (SELECT AVG(a.order_value) FROM Orders a
WHERE a.customer_id = o.customer_id AND a.status = 'delivered')
ORDER BY o.customer_id, o.order_idAnother way
SELECT o.customer_id, o.order_id, o.order_value, ROUND(c.avg_value, 2) AS customer_avg
FROM Orders o
JOIN (SELECT customer_id, AVG(order_value) AS avg_value, SUM(order_value) AS total, COUNT(*) AS n
FROM Orders WHERE status = 'delivered' GROUP BY customer_id) c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered' AND o.order_value * c.n > c.total
ORDER BY o.customer_id, o.order_idAnother way
SELECT customer_id, order_id, order_value, ROUND(avg_value, 2) AS customer_avg
FROM (SELECT customer_id, order_id, order_value, AVG(order_value) OVER (PARTITION BY customer_id) AS avg_value,
SUM(order_value) OVER (PARTITION BY customer_id) AS total, COUNT(*) OVER (PARTITION BY customer_id) AS n
FROM Orders WHERE status = 'delivered') t
WHERE order_value * n > total
ORDER BY customer_id, order_id← Best-Selling Dish of Each Restaurant · Rider Login Hours and July Incentive Tier →