PostgreSQL - Common Table Expressions
You want to list all employees with their level in the hierarchy starting from the CEO (manager_id IS NULL). Which recursive CTE query correctly adds a level column showing depth?
You want to list all employees with their level in the hierarchy starting from the CEO (manager_id IS NULL). Which recursive CTE query correctly adds a level column showing depth?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Check anchor member level initialization
Level starts at 1 for CEO (manager_id IS NULL) in WITH RECURSIVE emp_levels 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, el.level + 1 FROM employees e JOIN emp_levels el ON e.manager_id = el.id ) SELECT * FROM emp_levels ORDER BY level; and D; WITH RECURSIVE emp_levels AS ( SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, level + 1 FROM employees e JOIN emp_levels el ON e.manager_id = el.id ) SELECT * FROM emp_levels ORDER BY level; starts at 0 which is acceptable but less common.Step 2: Check recursive member level calculation
WITH RECURSIVE emp_levels 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, el.level + 1 FROM employees e JOIN emp_levels el ON e.manager_id = el.id ) SELECT * FROM emp_levels ORDER BY level; correctly increments level by 1 (el.level + 1). WITH RECURSIVE emp_levels 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, el.level - 1 FROM employees e JOIN emp_levels el ON e.manager_id = el.id ) SELECT * FROM emp_levels ORDER BY level; incorrectly decrements level (el.level - 1). WITH RECURSIVE emp_levels AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN emp_levels el ON e.manager_id = el.id ) SELECT *, level FROM emp_levels ORDER BY level; lacks level column.Final Answer:
Option A correctly adds and increments level in recursion -> Option AQuick Check:
Level increments by 1 each recursion [OK]
Quick Trick: Add level column and increment in recursive part [OK]
Common Mistakes:
- Forgetting to add level column in recursive part
- Decrementing level instead of incrementing
- Not initializing level in anchor member
Master "Common Table Expressions" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes