[Solved] You want to calculate the average order amount per customer and then find customers whose average order is above 100. Which query correctly uses multiple CTEs to achieve this? | PostgreSQL

PostgreSQL - Common Table Expressions

You want to calculate the average order amount per customer and then find customers whose average order is above 100. Which query correctly uses multiple CTEs to achieve this?

AWITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg;

BWITH avg_orders AS (SELECT customer_id, SUM(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg;

CWITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg;

DWITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id) SELECT * FROM avg_orders WHERE avg_amount > 100;

Step-by-Step Solution

Solution:

Step 1: Calculate average order per customer
WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; correctly uses AVG(amount) with GROUP BY customer_id in avg_orders CTE.
Step 2: Filter customers with avg_amount > 100
WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; uses a second CTE high_avg to select customers meeting the condition.
Step 3: Final SELECT returns customers with high average orders
WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; selects all from high_avg, giving correct result.
Final Answer:
WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; -> Option A
Quick Check:
Correct aggregation and filtering = WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; [OK]

Quick Trick: Use one CTE for aggregation, another for filtering [OK]

Common Mistakes:

Using SUM instead of AVG for average calculation
Missing GROUP BY clause
Not filtering in a separate CTE

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

You want to calculate the average order amount per customer and then find customers whose average order is above 100. Which query correctly uses multiple CTEs to achieve this?

Step 1: Calculate average order per customer

Step 2: Filter customers with avg_amount > 100

Step 3: Final SELECT returns customers with high average orders

Final Answer:

Quick Check:

Want More Practice?