Recap - Org Chart

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 22 of 27.

Challenge

Medium

Available tables and columns:
employees: id, name, manager_id

For the employee with id = 7, return the chain of managers above them: their direct manager, their manager's manager, and so on up to the top of the org. Return id and name, ordered from the closest manager (lowest depth) to the most senior. Do not include employee 7 themselves.

Try it yourself

WITH RECURSIVE chain AS (
    -- start from employee 7's manager
    -- recurse upward via manager_id
)
SELECT id, name FROM chain ORDER BY depth

All lessons in Beyond the Basics

1Conditional Logic

CASE WHEN CASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a HierarchyRecap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers