Recursive CTE concept in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple recursive CTE

Given the following SQL query using a recursive CTE, what will be the output?

SQL

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

A SyntaxError

B [{"n":1}]

C [{"n":1},{"n":2},{"n":3}]

D [{"n":1},{"n":2},{"n":3},{"n":4}]

💡 Hint

Think about how the recursive part adds rows until the condition is false.

The CTE starts with 1, then recursively adds 1 until n reaches 3, so the output is 1, 2, 3.

📝 Syntax

intermediate

Identify the syntax error in recursive CTE

Which option contains a syntax error in the recursive CTE definition?

A WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 cte WHERE val < 5) SELECT * FROM cte;

B WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte WHERE val <= 5) SELECT * FROM cte;

C WITH RECURSIVE cte AS (SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte WHERE val < 5) SELECT * FROM cte;

D ;etc MORF * TCELES )5 < lav EREHW etc MORF 1 + lav TCELES LLA NOINU lav SA 1 TCELES( SA etc EVISRUCER HTIW

💡 Hint

Look carefully at the recursive SELECT statement syntax.

Option A is missing the FROM keyword before cte, causing a syntax error.

❓ optimization

advanced

Optimizing recursive CTE to prevent infinite loops

Which option correctly prevents an infinite loop in this recursive CTE?

SQL

WITH RECURSIVE cte AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM cte WHERE num < 1000
)
SELECT * FROM cte;

A Add a LIMIT 1000 at the end of the query

B Remove the base SELECT statement

C Use UNION instead of UNION ALL

D Add a WHERE clause in the recursive part: WHERE num < 1000

💡 Hint

Think about how the recursion stops.

The WHERE clause in the recursive part stops recursion when num reaches 1000, preventing infinite loops.

🧠 Conceptual

advanced

Understanding recursive CTE base and recursive parts

In a recursive CTE, what is the role of the base query and the recursive query?

A Base query provides initial rows; recursive query adds rows based on previous results

B Both queries run simultaneously without dependency

C Recursive query runs only once; base query runs repeatedly

D Base query runs after recursive query finishes

💡 Hint

Think about how recursion builds results step by step.

The base query starts the recursion with initial rows; the recursive query repeatedly adds rows until a condition stops it.

🔧 Debug

expert

Debugging unexpected output in recursive CTE

Given this recursive CTE, why does it produce duplicate rows?

SQL

WITH RECURSIVE cte AS (
  SELECT 1 AS val
  UNION ALL
  SELECT val + 1 FROM cte WHERE val < 3
  UNION ALL
  SELECT val + 1 FROM cte WHERE val < 3
)
SELECT * FROM cte ORDER BY val;

A Because the WHERE clause is incorrect

B Because the recursive part has two UNION ALL clauses causing duplicates

C Because the base query returns duplicates

D Because the query is missing RECURSIVE keyword

💡 Hint

Look at how many recursive SELECTs are combined.

Having two UNION ALL recursive parts adds the same rows twice, causing duplicates.