WITH clause syntax in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple WITH clause query

What is the output of this query using a WITH clause to calculate doubled values?

PostgreSQL

WITH doubled AS (SELECT 1 AS num, 2 AS val UNION ALL SELECT 2, 4 UNION ALL SELECT 3, 6) SELECT num, val FROM doubled WHERE val > 3 ORDER BY num;

A

num | val
----+-----
  2 |   4
  3 |   6

B

num | val
----+-----
  1 |   2
  2 |   4
  3 |   6

C

num | val
----+-----
  1 |   2

D

num | val
----+-----
  3 |   6

💡 Hint

Look at the WHERE clause filtering values greater than 3.

The WITH clause defines a temporary table 'doubled'. The SELECT filters rows where val > 3, so only rows with val 4 and 6 remain.

📝 Syntax

intermediate

Identify the syntax error in WITH clause usage

Which option contains a syntax error in the WITH clause usage?

PostgreSQL

WITH temp AS SELECT id, name FROM users SELECT * FROM temp;

A WITH temp AS SELECT id, name FROM users; SELECT * FROM temp;

B WITH temp AS (SELECT id, name FROM users) SELECT * FROM temp;

C ;pmet MORF * TCELES )sresu MORF eman ,di TCELES( SA pmet HTIW

D WITH temp AS (SELECT id, name FROM users) SELECT * FROM temp

💡 Hint

Check if the WITH clause has parentheses around the subquery.

The WITH clause requires the subquery to be enclosed in parentheses. Option A misses parentheses and has an extra semicolon breaking the query.

❓ optimization

advanced

Optimizing multiple WITH clauses

Given two WITH clauses that calculate sums separately, which option combines them efficiently into one WITH clause?

PostgreSQL

WITH sum_a AS (SELECT SUM(amount) AS total_a FROM sales WHERE category = 'A'), sum_b AS (SELECT SUM(amount) AS total_b FROM sales WHERE category = 'B') SELECT total_a, total_b FROM sum_a CROSS JOIN sum_b;

A WITH sum_a AS (SELECT SUM(amount) AS total_a FROM sales WHERE category = 'A'), sum_b AS (SELECT SUM(amount) AS total_b FROM sales WHERE category = 'B') SELECT total_a, total_b FROM sum_a JOIN sum_b ON sum_a.total_a = sum_b.total_b;

B WITH sums AS (SELECT SUM(amount) AS total_a FROM sales WHERE category = 'A' UNION ALL SELECT SUM(amount) AS total_b FROM sales WHERE category = 'B') SELECT * FROM sums;

C WITH sums AS (SELECT SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS total_a, SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS total_b FROM sales) SELECT total_a, total_b FROM sums;

D WITH sums AS (SELECT SUM(amount) AS total FROM sales) SELECT total FROM sums;

💡 Hint

Try to calculate both sums in a single scan of the sales table.

Option C uses conditional aggregation to compute both sums in one pass, which is more efficient than separate queries or joins.

🔧 Debug

advanced

Debugging recursive WITH clause

What error will this recursive WITH clause produce?

PostgreSQL

WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 3) SELECT * FROM nums;

A Empty result set

B Syntax error: missing parentheses

C Runtime error: infinite recursion

D Returns rows: 1, 2, 3

💡 Hint

Check the base case and recursion condition carefully.

The recursive CTE starts at 1 and adds 1 until n reaches 3, producing rows 1, 2, and 3 without error.

🧠 Conceptual

expert

Understanding scope of WITH clause aliases

Which statement about the scope of table aliases defined in a WITH clause is true?

A Aliases defined in a WITH clause become permanent tables in the database.

B Aliases defined in a WITH clause are visible only within that query and cannot be referenced outside it.

C Aliases defined in a WITH clause can be referenced in any subsequent query in the same session.

D Aliases defined in a WITH clause are global and accessible by all users until the database restarts.

💡 Hint

Think about temporary nature of WITH clause results.

WITH clause aliases are temporary and exist only for the duration of the query they are part of.

Practice

1. What is the main purpose of the WITH clause in PostgreSQL?

easy

A. To create indexes on columns

B. To permanently store data in a new table

C. To create temporary named query results called CTEs

D. To delete rows from a table

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines Common Table Expressions (CTEs) which are temporary named result sets used within a query.

2. Step 2: Differentiate from other SQL commands

   It does not create permanent tables, delete data, or create indexes; it only helps organize complex queries.

3. Final Answer:

   To create temporary named query results called CTEs -> Option C

4. Quick Check:

   WITH clause = Temporary named queries [OK]

Hint: WITH clause defines temporary named queries (CTEs) [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with DELETE or CREATE INDEX
• Assuming WITH modifies data directly

2. Which of the following is the correct syntax to start a query using a WITH clause in PostgreSQL?

easy

A. WITH cte_name SELECT * FROM table_name AS cte_name;

B. WITH (SELECT * FROM table_name) AS cte_name SELECT * FROM cte_name;

C. WITH cte_name = SELECT * FROM table_name; SELECT * FROM cte_name;

D. WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;

Solution

1. Step 1: Recall correct WITH clause syntax

   The correct syntax is: WITH cte_name AS (subquery) followed by the main SELECT using cte_name.

2. Step 2: Check each option

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; matches the correct syntax. Options A, B, and D have misplaced keywords or wrong order.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; -> Option D

4. Quick Check:

   WITH cte_name AS (query) = correct syntax [OK]

Hint: WITH clause uses AS (subquery) syntax [OK]

Common Mistakes:

• Omitting AS keyword
• Placing SELECT before AS
• Using = instead of AS

3. Given the query:

WITH cte AS (SELECT id, name FROM users WHERE active = true) SELECT * FROM cte WHERE id < 5;

What will this query return?

medium

A. All users with id less than 5 regardless of active status

B. All users with active = true and id less than 5

C. All users with active = false and id less than 5

D. Syntax error due to incorrect WHERE clause

Solution

1. Step 1: Analyze the CTE definition

   The CTE named 'cte' selects users where active = true, so only active users are included.

2. Step 2: Analyze the main query filtering

   The main query selects from 'cte' where id < 5, so it filters active users with id less than 5.

3. Final Answer:

   All users with active = true and id less than 5 -> Option B

4. Quick Check:

   CTE filters active=true, main query filters id<5 [OK]

Hint: CTE filters first, main query filters second [OK]

Common Mistakes:

• Ignoring CTE filter and only applying main WHERE
• Assuming all users regardless of active status
• Thinking syntax error due to WHERE

4. Identify the error in the following query:

WITH cte1 AS (SELECT * FROM orders), cte2 (SELECT * FROM customers) SELECT * FROM cte1 JOIN cte2 ON cte1.customer_id = cte2.id;

medium

A. Missing AS keyword before the second CTE's subquery

B. Incorrect JOIN syntax between cte1 and cte2

C. CTEs cannot be used together in one WITH clause

D. SELECT statement missing after WITH clause

Solution

1. Step 1: Check syntax of multiple CTEs

   Multiple CTEs must be separated by commas, each with the syntax: name AS (subquery).

2. Step 2: Identify error in second CTE

   The second CTE 'cte2' is missing the AS keyword before its subquery.

3. Final Answer:

   Missing AS keyword before the second CTE's subquery -> Option A

4. Quick Check:

   Each CTE needs AS (subquery) [OK]

Hint: Each CTE must have AS before its query [OK]

Common Mistakes:

• Omitting AS keyword in CTE definition
• Misplacing commas between CTEs
• Incorrect JOIN syntax confusion

5. You want to write a query that uses two CTEs: one to get all active users, and another to get their orders. Which of the following is the correct way to write this using the WITH clause?

hard

A. WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

B. WITH active_users (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

C. WITH active_users AS SELECT * FROM users WHERE active = true, user_orders AS (SELECT * FROM orders) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

D. WITH active_users AS (SELECT * FROM users WHERE active = true) user_orders AS (SELECT * FROM orders) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

Solution

1. Step 1: Verify correct syntax for multiple CTEs

   Multiple CTEs must be separated by commas, each defined as name AS (subquery).

2. Step 2: Check each option's syntax and logic

   WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id; correctly uses AS and commas, and references active_users in user_orders. Options B, C, and D have syntax errors or missing commas.

3. Final Answer:

   WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas with AS (query) [OK]

Hint: Separate multiple CTEs with commas and use AS (query) [OK]

Common Mistakes:

• Missing commas between CTEs
• Omitting AS keyword
• Not referencing CTEs properly in later queries