Multiple CTEs in one query in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a CTE named 'first_cte'.

SQL

WITH [1] AS (SELECT * FROM employees)

Drag options to blanks, or click blank then click option'

ASELECT

Bemployees

CFROM

Dfirst_cte

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQL keywords like SELECT or FROM as the CTE name.

Skipping the CTE name after WITH.

The WITH clause starts a CTE, and you must name it. Here, 'first_cte' is the correct CTE name.

2fill in blank

medium

Complete the code to add a second CTE named 'second_cte' after the first one.

SQL

WITH first_cte AS (SELECT * FROM employees), [1] AS (SELECT * FROM departments)

Drag options to blanks, or click blank then click option'

Adepartments

Bsecond_cte

Cemployees

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names like 'departments' as CTE names.

Forgetting the comma between CTEs.

When defining multiple CTEs, separate them with commas and give each a unique name. Here, 'second_cte' is the correct name for the second CTE.

3fill in blank

hard

Fix the error in the query by completing the code to select from both CTEs.

SQL

WITH first_cte AS (SELECT id, name FROM employees), second_cte AS (SELECT id, dept FROM departments) SELECT f.name, s.dept FROM [1] f JOIN second_cte s ON f.id = s.id;

Drag options to blanks, or click blank then click option'

Afirst_cte

Bsecond_cte

Cemployees

Ddepartments

Attempts:

3 left

💡 Hint

Common Mistakes

Using original table names instead of CTE names in the main query.

Mixing up the CTE names.

The main query must select from the CTE names, not the original tables. Here, 'first_cte' is the correct CTE to join with 'second_cte'.

4fill in blank

hard

Fill both blanks to create two CTEs and select from them.

SQL

WITH [1] AS (SELECT id, salary FROM employees), [2] AS (SELECT id, bonus FROM bonuses) SELECT e.salary, b.bonus FROM e JOIN b ON e.id = b.id;

Drag options to blanks, or click blank then click option'

Ae

Bemployees

Cb

Dbonuses

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names instead of CTE names.

Not matching CTE names with aliases in the main query.

CTEs can be named anything. Here, 'e' and 'b' are short names for the CTEs, which are then used in the main query.

5fill in blank

hard

Fill all three blanks to define two CTEs and select the combined result.

SQL

WITH [1] AS (SELECT id, price FROM products), [2] AS (SELECT product_id, quantity FROM sales) SELECT p.price, s.quantity FROM [3] p JOIN sales s ON p.id = s.product_id;

Drag options to blanks, or click blank then click option'

Aproducts

Bsales

Dproduct_sales

Attempts:

3 left

💡 Hint

Common Mistakes

Using table names instead of CTE names.

Mismatching aliases and CTE names.

The first CTE is named 'products', the second 'sales'. The main query selects from 'products' aliased as 'p' and the 'sales' CTE.

Practice

1. What is the main purpose of using multiple CTEs (Common Table Expressions) in a single SQL query?

easy

A. To automatically create indexes on the tables used

B. To break down complex queries into simpler, named parts for better readability

C. To permanently store intermediate results in the database

D. To speed up the database engine by running queries in parallel

Solution

1. Step 1: Understand the role of CTEs

   CTEs are temporary named result sets used inside a query to organize complex logic.

2. Step 2: Identify the benefit of multiple CTEs

   Using multiple CTEs lets you split a big query into smaller, readable parts without permanent storage or indexing.

3. Final Answer:

   To break down complex queries into simpler, named parts for better readability -> Option B

4. Quick Check:

   Multiple CTEs improve query clarity = D [OK]

Hint: Multiple CTEs split queries into named parts for clarity [OK]

Common Mistakes:

• Thinking CTEs create permanent tables
• Assuming CTEs improve query speed automatically
• Confusing CTEs with indexes

2. Which of the following is the correct syntax to define two CTEs named cte1 and cte2 in one SQL query?

easy

A. WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

B. WITH cte1 AS (SELECT * FROM table1) cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

C. WITH cte1 (SELECT * FROM table1); WITH cte2 (SELECT * FROM table2); SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

D. WITH cte1 = (SELECT * FROM table1), cte2 = (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Solution

1. Step 1: Recall correct CTE syntax

   Multiple CTEs are separated by commas after WITH, each defined as name AS (query).

2. Step 2: Check each option

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id; correctly uses commas and AS keyword; others miss commas or use wrong syntax.

3. Final Answer:

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas = B [OK]

Hint: Separate multiple CTEs with commas after WITH [OK]

Common Mistakes:

• Omitting commas between CTEs
• Using semicolons between CTEs
• Using equals sign instead of AS

3. Given the following SQL query with multiple CTEs, what will be the output?

WITH cte1 AS (SELECT 1 AS num UNION ALL SELECT 2), cte2 AS (SELECT num FROM cte1 WHERE num = 2) SELECT * FROM cte2;

medium

A. 1 and 2

B. 1

C. 2

D. No rows returned

Solution

1. Step 1: Analyze cte1 content

   cte1 returns two rows with num values 1 and 2.

2. Step 2: Analyze cte2 filtering

   cte2 selects only rows from cte1 where num = 2, so only one row with num=2.

3. Final Answer:

   2 -> Option C

4. Quick Check:

   cte2 filters num=2 = A [OK]

Hint: Trace each CTE step-by-step to find final output [OK]

Common Mistakes:

• Assuming cte2 returns all rows from cte1
• Confusing UNION ALL with UNION
• Ignoring the WHERE filter in cte2

4. Identify the error in the following SQL query using multiple CTEs:

WITH cte1 AS (SELECT id, name FROM users) SELECT * FROM cte1; WITH cte2 AS (SELECT id FROM orders) SELECT * FROM cte2;

medium

A. CTEs cannot select from tables

B. CTEs must be named with different names

C. Missing JOIN condition between cte1 and cte2

D. Multiple WITH clauses are not allowed in one query

Solution

1. Step 1: Review CTE syntax rules

   Only one WITH clause is allowed per query; multiple CTEs must be separated by commas inside one WITH.

2. Step 2: Identify the error in the query

   The query uses two separate WITH clauses, which is invalid syntax.

3. Final Answer:

   Multiple WITH clauses are not allowed in one query -> Option D

4. Quick Check:

   Only one WITH clause per query = A [OK]

Hint: Use one WITH clause with commas for multiple CTEs [OK]

Common Mistakes:

• Writing multiple WITH clauses in one query
• Forgetting commas between CTEs
• Assuming CTEs create permanent tables

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

hard

A. 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;

B. WITH 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;

C. WITH avg_orders AS (SELECT customer_id, amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE amount > 100) SELECT * FROM high_avg;

D. WITH 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;

Solution

1. 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 groups orders by customer_id and calculates AVG(amount).

2. 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 to filter customers with average above 100, then selects from it.

3. 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

4. Quick Check:

   Multiple CTEs for calculation and filtering = C [OK]

Hint: Use one CTE to calculate, second to filter results [OK]

Common Mistakes:

• Missing GROUP BY when calculating averages
• Not filtering on aggregated column
• Incorrect CTE definitions without commas