Multiple CTEs in one query in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of multiple CTEs with aggregation

Given the following SQL query with two CTEs, what is the output result set?

SQL

WITH SalesCTE AS (
  SELECT salesperson_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY salesperson_id
),
TopSalesCTE AS (
  SELECT salesperson_id
  FROM SalesCTE
  WHERE total_sales > 10000
)
SELECT salesperson_id FROM TopSalesCTE ORDER BY salesperson_id;

A [{ "salesperson_id": 2 }, { "salesperson_id": 3 }, { "salesperson_id": 5 }]

B [{ "salesperson_id": 1 }, { "salesperson_id": 3 }]

C [{ "salesperson_id": 2 }, { "salesperson_id": 5 }]

D []

💡 Hint

Look at the total sales per salesperson and filter those with sales above 10000.

The first CTE calculates total sales per salesperson. The second CTE filters those with total sales greater than 10000. Only salesperson_id 2 and 5 meet this condition.

📝 Syntax

intermediate

Identify the syntax error in multiple CTEs

Which option contains a syntax error in defining multiple CTEs?

SQL

WITH FirstCTE AS (SELECT id FROM table1),
SecondCTE AS (SELECT id FROM table2)
SELECT * FROM FirstCTE JOIN SecondCTE ON FirstCTE.id = SecondCTE.id;

A WITH FirstCTE AS (SELECT id FROM table1) SecondCTE AS (SELECT id FROM table2) SELECT * FROM FirstCTE JOIN SecondCTE ON FirstCTE.id = SecondCTE.id;

B ;di.ETCdnoceS = di.ETCtsriF NO ETCdnoceS NIOJ ETCtsriF MORF * TCELES )2elbat MORF di TCELES( SA ETCdnoceS ,)1elbat MORF di TCELES( SA ETCtsriF HTIW

C WITH FirstCTE AS (SELECT id FROM table1), SecondCTE AS (SELECT id FROM table2); SELECT * FROM FirstCTE JOIN SecondCTE ON FirstCTE.id = SecondCTE.id;

D WITH FirstCTE AS (SELECT id FROM table1), SecondCTE AS (SELECT id FROM table2) SELECT * FROM FirstCTE JOIN SecondCTE ON FirstCTE.id = SecondCTE.id;

💡 Hint

Check the commas separating CTEs and the placement of the SELECT statement.

Option A is missing a comma between the two CTE definitions, causing a syntax error.

❓ optimization

advanced

Optimizing multiple CTEs for performance

You have two CTEs: one calculates total sales per product, the other filters products with sales above 5000. Which option optimizes the query to avoid scanning the sales table twice?

SQL

WITH TotalSales AS (
  SELECT product_id, SUM(amount) AS total_amount
  FROM sales
  GROUP BY product_id
),
FilteredProducts AS (
  SELECT product_id
  FROM TotalSales
  WHERE total_amount > 5000
)
SELECT * FROM FilteredProducts;

A Use a subquery inside the WHERE clause instead of CTEs.

B Combine both CTEs into one by filtering inside the aggregation CTE.

C Create an index on sales.amount to speed up aggregation.

D Use two separate queries instead of CTEs.

💡 Hint

Think about reducing repeated scans of the same table.

Combining filtering inside the aggregation CTE reduces scanning the sales table twice, improving performance.

🔧 Debug

advanced

Debugging incorrect output from multiple CTEs

A query with two CTEs returns no rows, but you expect some results. Which option explains the most likely cause?

SQL

WITH CTE1 AS (
  SELECT id FROM table1 WHERE status = 'active'
),
CTE2 AS (
  SELECT id FROM table2 WHERE id IN (SELECT id FROM CTE1)
)
SELECT * FROM CTE2;

A CTE1 returns no rows because no rows have status 'active', so CTE2 has no matching ids.

B CTE2 is missing a JOIN clause to connect with CTE1.

C The query is missing a final SELECT statement to output results.

D CTE2 uses an invalid subquery syntax causing it to return no rows.

💡 Hint

Check the filtering condition in the first CTE.

If CTE1 returns no rows, then the subquery in CTE2 finds no ids, resulting in no output.

🧠 Conceptual

expert

Understanding execution order of multiple CTEs

In a query with multiple CTEs, which statement best describes the execution order?

A CTEs are executed after the main query completes.

B All CTEs are executed in the order they are defined before the main query runs.

C CTEs are executed simultaneously in parallel before the main query.

D CTEs are executed only when referenced by the main query or other CTEs, not necessarily all.

💡 Hint

Think about whether unused CTEs run or not.

CTEs behave like inline views and are executed only when referenced, not all upfront.