Orders above the average of the same customer

Medium

Question

For each customer, calculate the average amount of THEIR OWN orders. Return the orders whose amount is higher than the average of the same customer.

An order that is equal to the average is not higher. A customer with only one order never has an order above their own average.

Return order_id, customer_id and amount. The order of the rows does not matter.

subquery
correlated subquery
cte
window function
avg

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders4 columns · 12 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount INTEGER NOT NULL, ordered_at TEXT NOT NULL);

Example rows of the orders table
id	customer_id	amount	ordered_at
1	1	100	2026-02-01
2	1	300	2026-02-04
3	1	200	2026-02-09
4	2	500	2026-02-02
5	2	500	2026-02-05
6	3	80	2026-02-03
7	4	120	2026-02-02
8	4	60	2026-02-06
9	4	90	2026-02-08
10	4	450	2026-02-10
11	5	1000	2026-02-07
12	5	20	2026-02-11

About this SQL

A correlated subquery uses a column of the outer row (here o.customer_id) and is evaluated for each outer row. It works the same in every database. A window function AVG(amount) OVER (PARTITION BY customer_id) gives the same number without a second pass, but you must filter on it in an outer query, because window functions are not allowed in WHERE.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready