You have a table employees(id INT, manager_id INT) representing a company hierarchy. How would you write a recursive CTE to find all employees under manager with id = 10, including indirect reports?
hard📝 Application Q15 of 15
PostgreSQL - Common Table Expressions
You have a table employees(id INT, manager_id INT) representing a company hierarchy. How would you write a recursive CTE to find all employees under manager with id = 10, including indirect reports?
A<pre>WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.manager_id = s.id
) SELECT * FROM subordinates;</pre>
B<pre>WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.id = s.manager_id
) SELECT * FROM subordinates;</pre>
C<pre>WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.manager_id FROM employees e JOIN subordinates s ON e.id = s.id
) SELECT * FROM subordinates;</pre>
D<pre>WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.id = s.manager_id
) SELECT * FROM subordinates;</pre>
Step-by-Step Solution
Solution:
Step 1: Identify the anchor query
The anchor selects employees directly reporting to manager 10: WHERE manager_id = 10.
Step 2: Understand the recursive join
The recursive part joins employees whose manager_id matches any previously found employee id, thus finding indirect reports.
Final Answer:
WITH RECURSIVE subordinates AS ( SELECT id FROM employees WHERE manager_id = 10 UNION ALL SELECT e.id FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates; -> Option A
Quick Check:
Join on e.manager_id = s.id to find subordinates [OK]
Quick Trick:Join on manager_id = previous employee id to find reports [OK]
Common Mistakes:
Swapping join conditions (id vs manager_id)
Starting anchor with wrong WHERE clause
Selecting wrong columns in recursive part
Master "Common Table Expressions" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
