How can a CTE be used to find customers whose total order amount exceeds the average total order amount across all customers?

hard📝 Application Q9 of 15

SQL - Common Table Expressions (CTEs)

ACTEs cannot be used for such calculations

BUse a single query without CTEs by nesting subqueries only

CUse a CTE to calculate average order amount per order, then filter customers by that

DUse a CTE to calculate total per customer, then filter those above the overall average computed in a separate CTE

Step-by-Step Solution

Solution:

Step 1: Calculate total order amount per customer
Create a CTE that sums orders grouped by customer_id.
Step 2: Calculate overall average total order amount
Create another CTE or subquery that computes the average of these totals.
Step 3: Filter customers
Select customers from the first CTE whose totals exceed the average from the second CTE.
Final Answer:
Use a CTE to calculate total per customer, then filter those above the overall average computed in a separate CTE -> Option D
Quick Check:
Use multiple CTEs for stepwise calculations [OK]

Quick Trick: Use multiple CTEs for stepwise filtering [OK]

Common Mistakes:

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes