Multiple CTEs in one query in PostgreSQL - Step-by-Step Execution

Concept Flow - Multiple CTEs in one query

Start Query

↓

Define CTE 1

↓

Define CTE 2

↓

Use CTEs in Main Query

↓

Execute Main Query

↓

Return Result

The query starts by defining multiple CTEs one after another, then uses them in the main query to produce the final result.

Execution Sample

PostgreSQL

WITH cte1 AS (
  SELECT 1 AS num
),
cte2 AS (
  SELECT num + 1 AS num_plus_one FROM cte1
)
SELECT * FROM cte2;

This query defines two CTEs: cte1 with a number 1, and cte2 which adds 1 to cte1's number, then selects from cte2.

Execution Table

Step	Action	CTE1 Result	CTE2 Result	Main Query Output
1	Evaluate cte1	[{num: 1}]	N/A	N/A
2	Evaluate cte2 using cte1	[{num: 1}]	[{num_plus_one: 2}]	N/A
3	Execute main query selecting from cte2	[{num: 1}]	[{num_plus_one: 2}]	[{num_plus_one: 2}]
4	End of query execution	[{num: 1}]	[{num_plus_one: 2}]	[{num_plus_one: 2}]

💡 All CTEs evaluated and main query executed, returning final result.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
cte1	undefined	[{num: 1}]	[{num: 1}]	[{num: 1}]
cte2	undefined	undefined	[{num_plus_one: 2}]	[{num_plus_one: 2}]
main_query_output	undefined	undefined	undefined	[{num_plus_one: 2}]

Key Moments - 2 Insights

Why does cte2 depend on cte1's result?

Because cte2 uses cte1 in its SELECT statement, it must wait until cte1 is evaluated (see execution_table step 2).

Can the main query use both cte1 and cte2?

Yes, the main query can use any or all defined CTEs after they are evaluated (see execution_table step 3).

Visual Quiz - 3 Questions

Look at the execution table, what is the output of cte1 after step 1?

A [{num_plus_one: 2}]

B [{num: 1}]

C N/A

D undefined

💡 Hint

Check the 'CTE1 Result' column at step 1 in the execution_table.

At which step does the main query produce its output?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Main Query Output' column in the execution_table.

If cte2 did not use cte1, how would the execution_table change?

A cte2 would be evaluated independently at step 2

B cte1 would not be evaluated

C Main query would run before cte2

D There would be no main query output

💡 Hint

Consider dependency order shown in execution_table steps 1 and 2.

Concept Snapshot

WITH cte1 AS (subquery1),
     cte2 AS (subquery2),
     ...
SELECT ... FROM cte1, cte2;

- Define multiple CTEs separated by commas.
- Later CTEs can use earlier ones.
- Main query uses these CTEs to get final results.

Full Transcript

This visual execution shows how multiple Common Table Expressions (CTEs) work in one PostgreSQL query. First, cte1 is defined and evaluated, producing a result. Then cte2 is defined using cte1's result and evaluated. Finally, the main query selects from cte2 and returns the final output. Variables track the state of each CTE and the main query output after each step. Key moments clarify dependencies between CTEs and usage in the main query. The quiz tests understanding of the evaluation order and outputs.

Practice

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

easy

A. To speed up the database server hardware

B. To create multiple databases at once

C. To permanently store data in the database

D. To break a complex query into smaller, readable parts

Solution

1. Step 1: Understand what CTEs do

   CTEs let you write parts of a query separately with names, making complex queries easier to read.

2. Step 2: Identify the purpose of multiple CTEs

   Using multiple CTEs helps organize and simplify complex logic by splitting it into smaller named queries.

3. Final Answer:

   To break a complex query into smaller, readable parts -> Option D

4. Quick Check:

   Multiple CTEs = Query readability [OK]

Hint: Think of CTEs as named steps in a recipe [OK]

Common Mistakes:

• Confusing CTEs with permanent tables
• Thinking CTEs improve hardware speed
• Assuming CTEs create databases

2. Which of the following is the correct syntax to define two CTEs named cte1 and cte2 in one PostgreSQL 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 (SELECT * FROM table1); WITH cte2 (SELECT * FROM table2); SELECT * FROM cte1, cte2;

C. WITH cte1 = SELECT * FROM table1, cte2 = SELECT * FROM table2 SELECT * FROM cte1 JOIN cte2;

D. WITH cte1 AS SELECT * FROM table1, cte2 AS SELECT * FROM table2 SELECT * FROM cte1 JOIN cte2;

Solution

1. Step 1: Recall correct CTE syntax

   CTEs start with WITH, each named, followed by AS and a query in parentheses, separated by commas.

2. Step 2: Check each option's syntax

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

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:

   Correct CTE syntax = WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id; [OK]

Hint: Remember: WITH cte_name AS (query), separate multiple CTEs by commas [OK]

Common Mistakes:

• Omitting parentheses around CTE queries
• Using semicolons between CTEs
• Using '=' instead of AS

3. Given the following query, what will be the output?

WITH cte1 AS (SELECT 1 AS num), cte2 AS (SELECT num * 2 AS double_num FROM cte1) SELECT * FROM cte2;

medium

A. [{num: 2}]

B. [{num: 1, double_num: 2}]

C. [{double_num: 2}]

D. Syntax error

Solution

1. Step 1: Analyze cte1

   cte1 returns one row with column num = 1.

2. Step 2: Analyze cte2 using cte1

   cte2 selects num * 2 as double_num, so double_num = 1 * 2 = 2.

3. Step 3: Final SELECT from cte2

   Only double_num column is selected, so output is [{double_num: 2}].

4. Final Answer:

   [{double_num: 2}] -> Option C

5. Quick Check:

   cte2 output columns = double_num [OK]

Hint: Check which columns each CTE outputs carefully [OK]

Common Mistakes:

• Assuming cte2 includes all columns from cte1
• Expecting num column in final output
• Thinking query has syntax errors

4. Identify the error in this query using multiple CTEs:

WITH cte1 AS (SELECT id, name FROM users), cte2 AS (SELECT id, age FROM users) SELECT cte1.id, cte1.name, cte2.age FROM cte1 JOIN cte2 ON cte1.id = cte2.user_id;

medium

A. CTEs must be defined with semicolons

B. Column cte2.user_id does not exist

C. Missing comma between CTEs

D. Cannot join two CTEs in one query

Solution

1. Step 1: Check CTE definitions

   Both cte1 and cte2 select id and other columns from users table correctly.

2. Step 2: Check JOIN condition

   Query joins on cte1.id = cte2.user_id, but cte2 has no user_id column, only id.

3. Step 3: Identify error

   Using cte2.user_id causes a column not found error.

4. Final Answer:

   Column cte2.user_id does not exist -> Option B

5. Quick Check:

   Check column names in JOIN = Column cte2.user_id does not exist [OK]

Hint: Verify column names in JOIN conditions carefully [OK]

Common Mistakes:

• Assuming user_id exists in cte2
• Thinking semicolons separate CTEs
• Believing CTEs cannot be joined

5. 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?

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, 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;

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

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 uses AVG(amount) with GROUP BY customer_id in avg_orders CTE.

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 high_avg to select customers meeting the condition.

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

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

5. 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]

Hint: 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