Why CTEs matter in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple CTE query

What is the output of this PostgreSQL query using a CTE?

PostgreSQL

WITH numbers AS (SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3) SELECT num * 2 AS doubled FROM numbers ORDER BY num;

A 1, 2, 3

B 3, 6, 9

C 2, 4, 6

D Error: syntax error

💡 Hint

Think about what the CTE 'numbers' contains and how the SELECT multiplies each value.

The CTE 'numbers' creates a temporary table with values 1, 2, and 3. The main query multiplies each by 2, so the output is 2, 4, 6.

🧠 Conceptual

intermediate

Why use CTEs for readability?

Why do developers use Common Table Expressions (CTEs) in PostgreSQL queries?

A To store data permanently in the database

B To make queries easier to read and organize complex logic step-by-step

C To replace indexes on tables for faster lookups

D To speed up query execution by caching results automatically

💡 Hint

Think about how breaking a big problem into smaller parts helps understanding.

CTEs help break complex queries into smaller, named parts, making them easier to read and maintain.

❓ optimization

advanced

Effect of CTEs on query optimization

In PostgreSQL, how do CTEs affect query optimization compared to subqueries?

A CTEs act as optimization fences and are executed separately before the main query

B CTEs are always inlined and optimized with the main query

C CTEs automatically create indexes for faster access

D CTEs prevent the query planner from using any indexes

💡 Hint

Consider how PostgreSQL treats CTEs as separate steps in query execution.

In PostgreSQL, CTEs are optimization fences, meaning they are executed independently before the main query, which can affect performance.

🔧 Debug

advanced

Identify the error in this CTE query

What error will this PostgreSQL query raise?

PostgreSQL

WITH cte AS (SELECT id, name FROM users) SELECT id, age FROM cte;

A Error: column "age" does not exist

B Error: syntax error near SELECT

C Error: missing FROM clause

D No error, returns id and age columns

💡 Hint

Check which columns are selected inside the CTE and which are requested outside.

The CTE selects only 'id' and 'name'. The outer query requests 'id' and 'age', but 'age' is not in the CTE, causing an error.

🧠 Conceptual

expert

When to avoid CTEs for performance

In which situation might using a CTE in PostgreSQL cause slower query performance?

A When the CTE is used to simplify query readability

B When the CTE is small and used only once

C When the CTE contains only constant values

D When the CTE is large and used multiple times in the main query

💡 Hint

Think about how PostgreSQL executes CTEs and the cost of repeated execution.

Because CTEs act as optimization fences, large CTEs used multiple times can be executed repeatedly, slowing down the query.