[Solved] Given these tables:- sales(year INT, amount INT)Write a query using two CTEs where the first CTE calculates total sales per year, and the second CTE selects only years with total sales above 1000.... | SQL

SQL - Common Table Expressions (CTEs)

Given these tables:
- sales(year INT, amount INT)
Write a query using two CTEs where the first CTE calculates total sales per year, and the second CTE selects only years with total sales above 1000. Which query is correct?

AWITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales GROUP BY year) SELECT * FROM total_sales WHERE total > 1000;

BWITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales;

CWITH total_sales AS (SELECT year, amount FROM sales GROUP BY year), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales;

DWITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales GROUP BY year), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales;

Step-by-Step Solution

Solution:

Step 1: Check aggregation in first CTE
WITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales GROUP BY year), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales; correctly sums amount per year with GROUP BY.
Step 2: Check filtering in second CTE
Second CTE filters years where total > 1000, referencing first CTE.
Step 3: Verify final SELECT
Final SELECT returns filtered years from second CTE.
Final Answer:
WITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales GROUP BY year), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales; -> Option D
Quick Check:
Two-step CTE query = WITH total_sales AS (SELECT year, SUM(amount) AS total FROM sales GROUP BY year), high_sales AS (SELECT year FROM total_sales WHERE total > 1000) SELECT * FROM high_sales; [OK]

Quick Trick: Use GROUP BY in first CTE, filter in second CTE [OK]

Common Mistakes:

Missing GROUP BY in aggregation
Selecting non-aggregated columns without GROUP BY
Filtering without referencing first CTE

Master "Common Table Expressions (CTEs)" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given these tables:

Step 1: Check aggregation in first CTE

Step 2: Check filtering in second CTE

Step 3: Verify final SELECT

Final Answer:

Quick Check:

Want More Practice?