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

Challenge - 5 Problems

❓ query_result

intermediate

Output of multiple CTEs with aggregation

Consider the following SQL query using two CTEs. What will be the output rows?

PostgreSQL

WITH sales AS (
  SELECT 'Alice' AS salesperson, 100 AS amount
  UNION ALL
  SELECT 'Bob', 150
  UNION ALL
  SELECT 'Alice', 200
),
summary AS (
  SELECT salesperson, SUM(amount) AS total_sales
  FROM sales
  GROUP BY salesperson
)
SELECT salesperson, total_sales FROM summary ORDER BY salesperson;

A [{"salesperson": "Alice", "total_sales": 100}, {"salesperson": "Bob", "total_sales": 150}]

B [{"salesperson": "Alice", "total_sales": 200}, {"salesperson": "Bob", "total_sales": 150}]

C [{"salesperson": "Alice", "total_sales": 300}, {"salesperson": "Bob", "total_sales": 150}]

D [{"salesperson": "Alice", "total_sales": 300}, {"salesperson": "Bob", "total_sales": 250}]

💡 Hint

Sum the amounts for each salesperson from the sales CTE.

The sales CTE lists amounts per salesperson. The summary CTE sums these amounts grouped by salesperson. Alice has 100 + 200 = 300, Bob has 150.

📝 Syntax

intermediate

Identify the syntax error in multiple CTEs

Which option contains a syntax error in defining multiple CTEs?

PostgreSQL

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

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

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

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

D WITH cte1 AS (SELECT 1 AS num) cte2 AS (SELECT 2 AS num) SELECT * FROM cte1 UNION ALL SELECT * FROM cte2;

💡 Hint

Check the commas and placement between CTE definitions.

Option D misses the comma between cte1 and cte2 definitions, causing a syntax error.

❓ optimization

advanced

Optimizing multiple CTEs for performance

Given two CTEs where the second depends on the first, which option optimizes the query to avoid repeated scans?

PostgreSQL

WITH cte1 AS (
  SELECT * FROM orders WHERE order_date >= '2024-01-01'
),
cte2 AS (
  SELECT customer_id, COUNT(*) AS order_count FROM cte1 GROUP BY customer_id
)
SELECT * FROM cte2 WHERE order_count > 5;

A Add an index on orders(order_date) and keep the CTEs as is.

B Use MATERIALIZED keyword explicitly for cte1 to force materialization.

C Replace cte1 with a subquery inside cte2 to avoid materializing cte1 separately.

D Rewrite cte2 as a JOIN on orders instead of using cte1.

💡 Hint

Indexes help speed up filtering on large tables.

Adding an index on order_date helps the initial filter in cte1 run faster, improving overall query speed without changing logic.

🔧 Debug

advanced

Debugging unexpected output from multiple CTEs

This query returns no rows, but you expect some. What is the cause?

PostgreSQL

WITH cte1 AS (
  SELECT * FROM products WHERE price > 100
),
cte2 AS (
  SELECT * FROM cte1 WHERE price < 50
)
SELECT * FROM cte2;

A The query needs DISTINCT in cte2 to return rows.

B The second CTE filters price < 50 but cte1 only has price > 100, so no rows match.

C The CTEs are not chained properly; cte2 should join cte1 instead of filtering.

D Missing semicolon after cte1 causes cte2 to fail silently.

💡 Hint

Check the filter conditions in both CTEs carefully.

cte1 selects products with price > 100, but cte2 filters for price < 50 on cte1 results, which is impossible, so no rows return.

🧠 Conceptual

expert

Understanding execution order of multiple CTEs

In a query with multiple CTEs where cte3 depends on cte2 and cte2 depends on cte1, which statement is true about execution order?

A CTEs are executed lazily only when referenced, so cte3 triggers cte2, which triggers cte1.

B All CTEs are executed simultaneously and results merged before the main query.

C CTEs are executed in the order they are defined, from cte1 to cte3, before the main query runs.

D CTEs are materialized in temporary tables in reverse order, starting from cte3.

💡 Hint

Think about how PostgreSQL handles CTEs and dependencies.

PostgreSQL executes CTEs lazily when referenced. cte3 depends on cte2, so cte3 triggers cte2, which triggers cte1 execution.