sqladvanced10 minutes

Advanced SQL Output Prediction: Recursive CTE with Window Functions

Analyze the given SQL query utilizing recursive Common Table Expressions (CTEs) combined with window functions, and predict the exact output it will generate based on the provided data.

Challenge prompt

Given the following tables and SQL query, predict the complete output including all columns and rows. Explain the reasoning behind the result, focusing on how the recursive CTE operates alongside window functions within the query. Tables: Employees(employee_id INT, manager_id INT, name VARCHAR) Data: employee_id | manager_id | name 1 | NULL | 'Alice' 2 | 1 | 'Bob' 3 | 1 | 'Charlie' 4 | 2 | 'Diana' 5 | 2 | 'Eve' Query: WITH RECURSIVE HierarchyCTE AS ( SELECT employee_id, manager_id, name, 1 AS level FROM Employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, e.name, h.level + 1 FROM Employees e INNER JOIN HierarchyCTE h ON e.manager_id = h.employee_id ) SELECT employee_id, name, level, ROW_NUMBER() OVER (PARTITION BY level ORDER BY employee_id) AS rn FROM HierarchyCTE ORDER BY level, rn;

Guidance

• Carefully track the recursion as the CTE builds the hierarchy level by level.
• Understand how ROW_NUMBER() is applied partitioned by the level to order employees within each level by employee_id.

Hints

• Start by identifying the employees with manager_id IS NULL for the base case of the recursion.
• For each subsequent recursion step, employees managed by those in the previous level are assigned a level number incremented by one.

Starter code

WITH RECURSIVE HierarchyCTE AS (
  SELECT employee_id, manager_id, name, 1 AS level
  FROM Employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name, h.level + 1
  FROM Employees e
  INNER JOIN HierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, level,
       ROW_NUMBER() OVER (PARTITION BY level ORDER BY employee_id) AS rn
FROM HierarchyCTE
ORDER BY level, rn;

Expected output

employee_id | name | level | rn 1 | Alice | 1 | 1 2 | Bob | 2 | 1 3 | Charlie | 2 | 2 4 | Diana | 3 | 1 5 | Eve | 3 | 2

Core concepts

Recursive CTEWindow FunctionsHierarchical QueriesROW_NUMBER()

Challenge a Friend

Send this duel to someone else and see if they can solve it.