I have a Databricks SQL table with the following columns:

This table contains hierarchical data for all employees in the organization, where each employee has an associated manager (except for the CEO, whose managerId is NULL).

I want to build a new table that flattens the hierarchy, so that for any given employee ID, I can retrieve all of their direct and indirect subordinates, along with each subordinate's level in the hierarchy

the code 

WITH RECURSIVE hierarchy AS (
-- Anchor: start with each employee as their own root (level 0)
SELECT
e.id AS root_id,
e.name AS root_name,
e.id AS emp_id,
e.name AS emp_name,
e.managerId,
0 AS level_id
FROM employees e

UNION ALL

-- Recursive part: find all subordinates
SELECT
h.root_id,
h.root_name,
e.id AS emp_id,
e.name AS emp_name,
e.managerId,
h.level_id + 1 AS level_id
FROM hierarchy h
JOIN employees e
ON e.managerId = h.emp_id
)
SELECT *
FROM hierarchy
ORDER BY root_id, level_id, emp_id;