Bird
0
0

How can you use multiple CTEs to first select orders with amount > 200, then compute total sales per customer?

hard📝 Application Q9 of 15
PostgreSQL - Common Table Expressions
How can you use multiple CTEs to first select orders with amount > 200, then compute total sales per customer?
AWITH filtered_orders AS (SELECT * FROM orders WHERE amount > 200), total_sales AS (SELECT customer_id, SUM(amount) AS total FROM filtered_orders GROUP BY customer_id) SELECT * FROM total_sales;
BWITH total_sales AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id), filtered_orders AS (SELECT * FROM total_sales WHERE total > 200) SELECT * FROM filtered_orders;
CWITH filtered_orders AS (SELECT * FROM orders WHERE amount > 200) SELECT customer_id, SUM(amount) AS total FROM filtered_orders;
DWITH total_sales AS (SELECT customer_id, SUM(amount) AS total FROM orders WHERE amount > 200 GROUP BY customer_id) SELECT * FROM total_sales;
Step-by-Step Solution
Solution:

  1. Step 1: Filter orders

    First CTE filters orders with amount > 200.

  2. Step 2: Aggregate sales

    Second CTE sums amounts per customer from filtered orders.

  3. Step 3: Final select

    Query selects all from total_sales CTE.

  4. Final Answer:

    Option A -> Option A

  5. Quick Check:

    Filter first, then aggregate [OK]
Quick Trick: Filter before aggregation in CTEs [OK]
Common Mistakes:
  • Aggregating before filtering
  • Filtering aggregated results incorrectly
  • Not grouping by customer_id

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Aggregate Functions and GROUP BY - ROLLUP and CUBE for hierarchical totals - Quiz 4medium Aggregate Functions and GROUP BY - GROUP BY single and multiple columns - Quiz 5medium Aggregate Functions and GROUP BY - ROLLUP and CUBE for hierarchical totals - Quiz 5medium Common Table Expressions - Recursive CTE for hierarchical data - Quiz 11easy Joins in PostgreSQL - NATURAL join and its risks - Quiz 7medium Set Operations and Advanced Queries - Conditional INSERT with ON CONFLICT - Quiz 4medium Subqueries in PostgreSQL - ALL, ANY, SOME with subqueries - Quiz 13medium Subqueries in PostgreSQL - ALL, ANY, SOME with subqueries - Quiz 2easy Subqueries in PostgreSQL - Subqueries in WHERE with IN - Quiz 10hard Views and Materialized Views - Views with CHECK OPTION - Quiz 15hard