Orders Above the Customer's Own Average Basket

Medium
159.

Orders Above the Customer's Own Average Basket

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

ColumnTypeValues
order_idint
customer_idint
placed_atdatetime
order_valueint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order, value in rupees.

Example

The rows must come back in the order shown.

Orders

order_idcustomer_idplaced_atorder_valuestatus
1512024-09-01 13:00:00300delivered
2512024-09-03 20:10:00500delivered
3512024-09-05 21:00:00900cancelled
4512024-09-08 13:30:00350delivered
5522024-09-02 19:45:00420delivered
6532024-09-04 12:15:00250delivered
7532024-09-06 12:20:00250delivered
8542024-09-07 20:00:00610delivered
9542024-09-09 20:30:00199delivered
10542024-09-10 21:15:00280delivered

Output

customer_idorder_idorder_valuecustomer_avg
512500383.33
548610363
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
customer_idint
placed_atdatetime
order_valueint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order, value in rupees.

Examples

Example 1

Orders

order_idcustomer_idplaced_atorder_valuestatus
1512024-09-01 13:00:00300delivered
2512024-09-03 20:10:00500delivered
3512024-09-05 21:00:00900cancelled
4512024-09-08 13:30:00350delivered
5522024-09-02 19:45:00420delivered
6532024-09-04 12:15:00250delivered
7532024-09-06 12:20:00250delivered
8542024-09-07 20:00:00610delivered
9542024-09-09 20:30:00199delivered
10542024-09-10 21:15:00280delivered

Output

customer_idorder_idorder_valuecustomer_avg
512500383.33
548610363

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_id

Another 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_id

Another 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 →