sqladvanced15 minutes

Fix the Recursive Sales Totals Query for Hierarchical Employees

You are given a broken SQL query intended to calculate total sales made by each employee and their subordinates in a hierarchical company structure. The query uses a recursive CTE but produces incorrect or incomplete totals. Your task is to identify and fix the bugs so that the query accurately computes cumulative sales totals including all levels of subordinates.

Challenge prompt

The database contains two tables: Employees and Sales. Employees has columns (EmployeeID, ManagerID) representing a hierarchy where each employee may have a manager. Sales has columns (SaleID, EmployeeID, Amount). The goal is to compute, for each employee, the total sales made by that employee and all their direct and indirect subordinates. A recursive CTE is written but returns incorrect totals or misses some employees. Fix the query so it returns correct cumulative sales amounts per employee. Tables example: Employees: EmployeeID | ManagerID 1 | NULL 2 | 1 3 | 1 4 | 2 5 | 2 Sales: SaleID | EmployeeID | Amount 100 | 1 | 1000 101 | 2 | 500 102 | 3 | 700 103 | 4 | 300 104 | 5 | 200 Your output should calculate totals like: EmployeeID | TotalSales 1 | 2700 (1000 + 500 + 700 + 300 + 200) 2 | 1000 (500 + 300 + 200) 3 | 700 4 | 300 5 | 200 Fix the SQL query so it computes such totals correctly.

Guidance

• Carefully check how the recursive CTE joins Employees to its subordinates each iteration.
• Make sure sales amounts are aggregated correctly after establishing the full hierarchy.
• Verify that the base and recursive parts of the CTE generate the correct set of rows with proper columns.

Hints

• Check whether the recursive CTE includes both the employee and their subordinates at each recursion level.
• Confirm that joining Sales is done after constructing the entire hierarchy, not during it.
• Make sure GROUP BY clauses are consistent with selected columns.

Starter code

WITH RECURSIVE EmpHierarchy AS (
  SELECT EmployeeID, ManagerID
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID
  FROM Employees e
  JOIN EmpHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT eh.EmployeeID, SUM(s.Amount) AS TotalSales
FROM EmpHierarchy eh
JOIN Sales s ON s.EmployeeID = eh.EmployeeID
GROUP BY eh.EmployeeID;

Expected output

EmployeeID | TotalSales 1 | 2700 2 | 1000 3 | 700 4 | 300 5 | 200

Core concepts

recursive CTEhierarchical queriesaggregationdebugging recursion

Challenge a Friend

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