SQL Recursive CTE

Background

Recursive CTEs reference themselves, enabling hierarchical traversal and graph algorithms in SQL.

Problem

Write a recursive SQL query to find all direct and indirect reports of a manager in an organizational hierarchy.

Input

An employees table with id, name, manager_id columns representing an org hierarchy.

Output

All direct and indirect reports of a given manager, with their hierarchy level.

Sample tests

Input: CEO -> VP -> Manager -> Employee, find all reports of VP

Expected: Manager and Employee with levels 1 and 2

Your solution

Learning objective

Learn recursive CTE structure, anchor and recursive members, and hierarchical traversal.