Use Subquery to Filter by Aggregate

SQL

Technical Specification

Select all orders whose total_amount is greater than the average order total for all orders.

Input/Output Samples

Input:orders table

Output:Orders above average value

Optimal Logic Path

SELECT *
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount) FROM orders
);

Architectural Deep-Dive

Subqueries allow you to compare each row against an aggregate computed across the table.