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.

Practice

1. What is the main purpose of using a Recursive CTE in SQL for series generation?

easy

A. To generate a sequence of numbers or dates step-by-step within a query

B. To join two tables based on a common column

C. To delete duplicate rows from a table

D. To create indexes for faster searching

Solution

1. Step 1: Understand Recursive CTE usage

   Recursive CTEs are used to repeatedly execute a query to build results step-by-step.

2. Step 2: Identify series generation purpose

   They are commonly used to generate sequences like numbers or dates without needing extra tables.

3. Final Answer:

   To generate a sequence of numbers or dates step-by-step within a query -> Option A

4. Quick Check:

   Recursive CTE = sequence generation [OK]

Hint: Recursive CTEs build sequences stepwise inside SQL [OK]

Common Mistakes:

• Confusing recursive CTE with JOIN operations
• Thinking recursive CTE deletes duplicates
• Assuming recursive CTE creates indexes

2. Which of the following is the correct syntax to start a recursive CTE named numbers generating numbers from 1?

easy

A. WITH RECURSIVE numbers AS SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5

B. WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5)

C. WITH numbers RECURSIVE AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5)

D. WITH RECURSIVE numbers (num) AS (SELECT 1 AS n UNION ALL SELECT num + 1 FROM numbers WHERE num < 5)

Solution

1. Step 1: Check correct WITH RECURSIVE syntax

   The correct syntax starts with WITH RECURSIVE cte_name AS ( ... ).

2. Step 2: Validate the recursive query structure

   WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5) correctly uses SELECT 1 AS num as anchor and recursive part with UNION ALL and stopping condition.

3. Final Answer:

   WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5) -> Option B

4. Quick Check:

   Correct recursive CTE syntax = WITH RECURSIVE numbers AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM numbers WHERE num < 5) [OK]

Hint: Start recursive CTE with WITH RECURSIVE cte AS (...) [OK]

Common Mistakes:

• Placing RECURSIVE after CTE name
• Missing parentheses around CTE query
• Omitting AS keyword

3. Given the query:

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 2 FROM seq WHERE n < 7
)
SELECT * FROM seq;

What will be the output rows?

medium

A. [1, 3, 5]

B. [1, 2, 3, 4, 5, 6, 7]

C. [1, 3, 5, 7]

D. [2, 4, 6, 8]

Solution

1. Step 1: Analyze anchor and recursive step

   Anchor row is 1. Recursive step adds 2 each time: 1, 3, 5, 7.

2. Step 2: Check stopping condition

   Recursion stops when n < 7 is false. Since 7 is included, sequence ends at 7.

3. Final Answer:

   [1, 3, 5, 7] -> Option C

4. Quick Check:

   Start=1, step=+2, stop at 7 = [1,3,5,7] [OK]

Hint: Add step value repeatedly until condition fails [OK]

Common Mistakes:

• Assuming step is 1 instead of 2
• Stopping before reaching 7
• Including numbers beyond 7

4. Identify the error in this recursive CTE:

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

medium

A. Using UNION instead of UNION ALL

B. Incorrect anchor query syntax

C. Wrong column alias in SELECT statements

D. Missing stopping condition inside the recursive part causing infinite recursion

Solution

1. Step 1: Check recursive part for stopping condition

   The recursive SELECT lacks a WHERE clause to stop recursion, causing infinite loop.

2. Step 2: Understand WHERE clause placement

   The WHERE clause outside the CTE filters final results but does not stop recursion inside CTE.

3. Final Answer:

   Missing stopping condition inside the recursive part causing infinite recursion -> Option D

4. Quick Check:

   Stopping condition must be inside recursive CTE [OK]

Hint: Put stopping condition inside recursive SELECT, not outside [OK]

Common Mistakes:

• Placing stopping condition only in final SELECT
• Confusing UNION and UNION ALL effects
• Incorrect alias usage

5. You want to generate a list of dates from '2024-01-01' to '2024-01-05' using a recursive CTE. Which query correctly produces this date series?

hard

A. WITH RECURSIVE dates AS (SELECT DATE('2024-01-01') AS dt UNION ALL SELECT dt + INTERVAL '1 day' FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates;

B. WITH RECURSIVE dates AS (SELECT '2024-01-01' AS dt UNION ALL SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt <= '2024-01-05') SELECT * FROM dates;

C. WITH RECURSIVE dates AS (SELECT '2024-01-01' AS dt UNION ALL SELECT dt + 1 FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates;

D. WITH RECURSIVE dates AS (SELECT DATE('2024-01-01') AS dt UNION ALL SELECT DATE(dt, '+1 day') FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates;

Solution

1. Step 1: Understand date addition syntax

   WITH RECURSIVE dates AS (SELECT DATE('2024-01-01') AS dt UNION ALL SELECT dt + INTERVAL '1 day' FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates; uses dt + INTERVAL '1 day' which is standard SQL for adding one day to a date.

2. Step 2: Check stopping condition and anchor

   Anchor selects the start date as DATE type. Recursive part adds one day while date is less than '2024-01-05'.

3. Step 3: Compare other options

   WITH RECURSIVE dates AS (SELECT DATE('2024-01-01') AS dt UNION ALL SELECT DATE(dt, '+1 day') FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates; uses SQLite style DATE(dt, '+1 day'), which is not standard SQL. WITH RECURSIVE dates AS (SELECT '2024-01-01' AS dt UNION ALL SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt <= '2024-01-05') SELECT * FROM dates; uses incorrect WHERE condition allowing extra date. WITH RECURSIVE dates AS (SELECT '2024-01-01' AS dt UNION ALL SELECT dt + 1 FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates; adds integer to date incorrectly.

4. Final Answer:

   WITH RECURSIVE dates AS (SELECT DATE('2024-01-01') AS dt UNION ALL SELECT dt + INTERVAL '1 day' FROM dates WHERE dt < '2024-01-05') SELECT * FROM dates; -> Option A

5. Quick Check:

   Date + INTERVAL '1 day' is standard SQL date addition [OK]

Hint: Use INTERVAL '1 day' to add days in standard SQL [OK]

Common Mistakes:

• Using database-specific date functions in standard SQL
• Incorrect stopping condition allowing extra dates
• Adding integer directly to date without INTERVAL