Orders Above Each Customers Average — Correlated Subqueries in SQL

The problem

Brightlane's sales team wants to identify order outliers — orders where a customer spent significantly more than their own typical purchase amount.

Write a query to return the order ID, customer ID, and total amount for every order whose total_amount exceeds that same customer's average order amount across all their orders.

Assumptions:

The orders table has one row per order with an id, a customer_id, and a total_amount.
A customer's average order amount is the average total_amount across every order linked to that customer_id.
Only orders whose total_amount is strictly greater than that customer's average should appear.

Output:

One row per qualifying order, with columns id, customer_id, and total_amount.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  customer_id,
  total_amount
FROM
  orders o
WHERE
  total_amount > (
    SELECT
      AVG(total_amount)
    FROM
      orders inner_o
    WHERE
      inner_o.customer_id = o.customer_id
  )

The shape

The threshold for "above this order's customer average" is itself a per-customer value, so the \WHERE\ condition uses a correlated subquery whose filter pins the inner \AVG\ to the outer order's customer. For each order being evaluated, the inner query computes that customer's average across all their orders, and the outer \WHERE\ keeps the order only when its \total_amount\ exceeds that customer-specific average.

Clause by clause

\SELECT id, customer_id, total_amount\ returns the three columns the spec asks for, straight from the \orders\ row that satisfies the filter.
\FROM orders o\ reads every order and aliases the table as \o\ so the inner subquery can reference \o.customer_id\ unambiguously.
\WHERE total_amount > (SELECT AVG(total_amount) FROM orders inner_o WHERE inner_o.customer_id = o.customer_id)\ is the correlated filter. The inner alias \inner_o\ distinguishes the inner orders rows from the outer \o\. The predicate \inner_o.customer_id = o.customer_id\ is what creates the correlation: the inner \AVG\ is computed across exactly the orders belonging to the same customer as the outer row. A customer with three orders of \100\, \200\, and \300\ has an average of \200\, so the order at \300\ qualifies and the order at \100\ does not.

Why this and not one global average

\WHERE total_amount > (SELECT AVG(total_amount) FROM orders)\ looks similar but answers a different question. That uncorrelated subquery computes one number across every order in the table and applies the same threshold to all customers. A customer whose orders are all small would never qualify even if one of their orders is twice their personal average, and a customer whose orders are all large would have every order qualify. The correlation is what makes the threshold customer-specific, which is what "exceeds that same customer's average" requires.

You practiced a correlated scalar subquery in WHERE — the average is recomputed per customer for each candidate row, yielding a per-row threshold that varies with the row's customer.

Return the order ID, customer ID, and total amount for every order whose `total_amount` exceeds that same customer's average order amount across all their orders

The shape

Clause by clause

Why this and not one global average

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.