CodeBlanks Logo Icon CodeBlanks
Login Register

Walk a hierarchy

Use a recursive CTE to walk a parent-child relationship.

Hierarchy query

With a recursive CTE you query manager-employee hierarchies. Each person reports to a parent.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

This query produces the organization tree.

WITH RECURSIVE defines a self-referencing temporary result set (CTE) named org; the query after it uses it like a table. All columns are selected from org.

Hierarchy query — details

The anchor picks the top manager, the recursive adds each subordinate level. level shows depth.

In the anchor WHERE manager_id IS NULL picks the person with no manager; in the recursive part employees are joined to those whose manager was found on the previous level.

Practice Hierarchy query

Write an org hierarchy query: anchor the root, recursively add subordinates.

Starter code

query.sql
-- hierarchy query
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level

Sample solution

query.sql
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Expected output

id  name    level
1   Fatma   1
2   Ayşe    2

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Walk a hierarchy: match the terms

Term Meaning
hierarchy tree
manager parent
subordinate child
level depth

2 Add level

Question 
  SELECT id, name, manager_id, 1 AS ____
Answer 
  SELECT id, name, manager_id, 1 AS level

Explanation: In the anchor query level starts at 1 and is increased by one on each recursion.

3 What does the anchor pick in a hierarchy query?

  • The top person Correct answer
  • Everyone
  • The bottom person
  • The leaves

Explanation: The anchor picks the top person.

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization