Intermediate#09 · Subquery, AVG

Average order value

What is the average value of a delivered order? Return one number, rounded to 2 decimals. First work out each order's total, then average those totals.

Write your query in the lab. It runs in your browser and tells you exactly what's wrong if it doesn't match.

Solve it in the lab

Hints

Hint 1

Build each order's total in a subquery: SELECT order_id, SUM(quantity * unit_price) AS total ... GROUP BY order_id.

Hint 2

Then SELECT ROUND(AVG(total), 2) FROM (...). Averaging line items directly gives the wrong answer.

Expected output

Your result should match this, in any order (1 row).

avg_order_value
406.98

Model answer

Show the solution

SELECT ROUND(AVG(total), 2) AS avg_order_value
FROM (
  SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.order_id
);

Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.

Next challenge: Monthly revenue, 2026 →