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.