Recursive CTE for series generation in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a Recursive CTE Generating Numbers 1 to 5

What is the output of this SQL query using a recursive CTE to generate numbers from 1 to 5?

SQL

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

A SyntaxError

B [{"num":1},{"num":2},{"num":3},{"num":4}]

C [{"num":0},{"num":1},{"num":2},{"num":3},{"num":4},{"num":5}]

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

💡 Hint

Look at the base case and the stopping condition in the recursive part.

The CTE starts at 1 and recursively adds 1 until it reaches 5, so the output is numbers 1 through 5 inclusive.

📝 Syntax

intermediate

Identify the Syntax Error in Recursive CTE

Which option contains a syntax error in this recursive CTE that generates numbers from 1 to 3?

SQL

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

A

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

B

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

C

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

D

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

💡 Hint

Check if the keyword RECURSIVE is required for recursive CTEs.

Option A misses the RECURSIVE keyword, which is required to define a recursive CTE in standard SQL.

❓ optimization

advanced

Optimizing Recursive CTE for Large Series

Which option is the most efficient way to generate numbers from 1 to 1000 using a recursive CTE?

A

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

B

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

C

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

D

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

💡 Hint

Check the stopping condition and whether the CTE is recursive.

Option C correctly uses RECURSIVE keyword and stops at n < 1000, generating numbers 1 to 1000 efficiently. Option C skips numbers by 10, missing many numbers. Option C uses <= 1000 in recursion, causing one extra recursion step but still works. Option C misses RECURSIVE keyword causing syntax error.

🔧 Debug

advanced

Why Does This Recursive CTE Cause an Infinite Loop?

Consider this recursive CTE: WITH RECURSIVE cte AS ( SELECT 1 AS val UNION ALL SELECT val + 1 FROM cte ) SELECT * FROM cte WHERE val <= 5; Why does this query cause an infinite loop or error?

A Because the recursive part has no stopping condition in the WHERE clause.

B Because the base case SELECT is missing a column alias.

C Because the CTE is missing the RECURSIVE keyword.

D Because the final SELECT filters values incorrectly.

💡 Hint

Look at the recursive SELECT and its WHERE clause.

The recursive SELECT does not have a WHERE clause to stop recursion, so it keeps adding 1 forever causing infinite recursion or error.

🧠 Conceptual

expert

Understanding Recursive CTE Execution Order

In a recursive CTE, which part executes first and how does the recursion proceed?

A The recursive member executes first to generate all rows, then the anchor member filters them.

B The anchor member executes first to produce initial rows, then the recursive member executes repeatedly using previous results until no new rows are produced.

C Both anchor and recursive members execute simultaneously and merge results at the end.

D The recursive member executes once, then the anchor member executes repeatedly.

💡 Hint

Think about how recursion builds results step by step.

Recursive CTEs start with the anchor member to get initial rows, then repeatedly apply the recursive member to add more rows until no new rows appear.