SQL Tutorial
•
•
4 min read
Recursive CTEs in SQL – How to Think and Solve Hierarchical & Sequence Problems (From Noob to Pro)
Recursive CTEs in SQL – How to Think and Solve Hierarchical & Sequence Problems (From Noob to Pro)
Hey, I’m Gaurav.
When I first saw `WITH RECURSIVE`, I was terrified.
The query had `UNION ALL` and referenced itself. My noob brain immediately thought:
“This looks like black magic. I’ll never understand this.”
The interviewer asked:
"Show the full hierarchy of employees under a manager."
I had no idea how to approach it.
Today, I’ll explain Recursive CTEs step by step — exactly how I went from confused to confident. We’ll cover real-world examples with clear thinking process.
You can practice all queries live on [sqlpractice.in](https://sqlpractice.in).
---
What is a Recursive CTE?
A Recursive CTE is a CTE that references itself.
It has two parts:
1. Anchor Member → The starting point (base case)
2. Recursive Member → The part that references the CTE itself and keeps going
Basic Syntax:
```sql
WITH RECURSIVE cte_name AS (
-- Anchor Member (starting point)
SELECT ...
FROM table
WHERE condition
UNION ALL
-- Recursive Member (keeps calling itself)
SELECT ...
FROM table t
JOIN cte_name r ON t.some_id = r.some_id
)
SELECT FROM cte_name;
```
---
Example 1: Generate Numbers (Simplest Recursive CTE)
Question: Generate numbers from 1 to 10.
Noob thinking: Use a loop or write 10 UNIONs.
Recursive thinking:
- Start with 1 (Anchor)
- Keep adding 1 until we reach 10 (Recursive)
Solution:
```sql
WITH RECURSIVE numbers AS (
SELECT 1 AS n -- Anchor Member
UNION ALL
SELECT n + 1 -- Recursive Member
FROM numbers
WHERE n < 10 -- Stopping condition
)
SELECT FROM numbers;
```
Practice Question 1 (Easy)
Generate numbers from 1 to 20 using recursive CTE.
[Try it live →](https://sqlpractice.in/?question=recursive-numbers)
---
Example 2: Employee Hierarchy (Most Common Interview Question)
Assume we have an `employees` table with `manager_id`:
```sql
-- Sample data
manager_id = NULL for CEO
manager_id points to another employee
```
Question: Show the full reporting hierarchy starting from the CEO.
Noob approach: Try to do multiple levels of self-joins (very messy, limited depth).
Better Recursive thinking:
1. Start with the CEO (manager_id IS NULL) → Anchor
2. Find all employees whose manager is in the previous level → Recursive
3. Keep going until no more employees are found
Solution:
```sql
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: Start with CEO
SELECT
id,
name,
manager_id,
0 AS level,
name AS hierarchy_path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: Find direct reports
SELECT
e.id,
e.name,
e.manager_id,
h.level + 1,
h.hierarchy_path || ' → ' || e.name
FROM employees e
JOIN employee_hierarchy h ON e.manager_id = h.id
)
SELECT
name,
level,
hierarchy_path
FROM employee_hierarchy
ORDER BY hierarchy_path;
```
Practice Question 2 (Medium)
Show the full employee hierarchy with levels and path.
[Try it live →](https://sqlpractice.in/?question=employee-hierarchy)
---
Example 3: Date Series Generation (Very Useful)
Question: Generate all dates between two given dates.
```sql
WITH RECURSIVE date_series AS (
SELECT '2026-01-01'::DATE AS date_value -- Anchor
UNION ALL
SELECT date_value + INTERVAL '1 day' -- Recursive
FROM date_series
WHERE date_value < '2026-01-31'
)
SELECT FROM date_series;
```
Practice Question 3 (Hard)
Generate daily dates from 1st March 2026 to 31st March 2026.
---
How to Think About Recursive CTEs (My Framework)
When you see a problem that needs recursion, ask:
1. What is the starting point? → This becomes the Anchor Member
2. What is the repeating pattern? → This becomes the Recursive Member
3. What is the stopping condition? → Put it in the `WHERE` clause of the recursive part
4. Do I need extra columns? (level, path, etc.) → Add them in both parts
Golden Rule:
Always put a stopping condition in the recursive member, otherwise you’ll get infinite recursion.
---
Common Real-World Use Cases of Recursive CTEs
- Employee / Organization hierarchy
- Category → Subcategory trees (product categories)
- Bill of Materials (parts explosion)
- Graph traversal (shortest path, friends of friends)
- Date / Number series generation
- Folder → Subfolder structure
---
Final Thoughts
Recursive CTEs looked scary at first, but once I broke them into Anchor + Recursive + Stopping condition, they became manageable.
The key mindset shift was:
> “I don’t need to solve the entire problem in one query. I just need to define the first step and the next step.”
Start practicing with simple number series, then move to employee hierarchy.
Go try these recursive CTE examples on [sqlpractice.in](https://sqlpractice.in) — especially the employee hierarchy one. It’s one of the most asked interview questions.
You’ll feel confident once you solve 3–4 recursive problems.
Keep going — you’re now entering advanced SQL territory!
Building sqlpractice.in to help everyone master SQL for interviews — completely free.