Department Hierarchy with TRUE Cumulative Budget Rollup
Build a recursive CTE that walks the department hierarchy AND computes cumulative_budget = own budget + every descendant department's budget. Use a second 'subtree' CTE that lists every (root, descendant) pair so cumulative_budget can be SUMmed. Return department_id, department_name, parent_department_id, budget, cumulative_budget, hierarchy_level — ordered by department_id.
- Recursive CTE
- CTEs
- Joins
- Subqueries
- Aggregation
Exercise brief
Understand the request
Finance planning director A planning report needs each department’s own budget plus every descendant department budget.
Return
- Return department attributes, cumulative budget, and hierarchy level.
- Order by department_id.
Constraints
- Build root-to-descendant pairs with recursion.
- Aggregate at the root department grain.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
departments
department_idINTEGERdepartment_nameTEXTparent_department_idINTEGERbudgetDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The naive 'just propagate budget down' solution does NOT roll up — it just relabels.
Hint 2
True rollup needs a second CTE producing (root, descendant) pairs, then SUM(budget) over each root's subtree.
Hint 3
Sanity check: Corporate's cumulative_budget should equal SUM of every department's budget.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE hierarchy AS (SELECT department_id, department_name, parent_department_id, budget, 1 AS hierarchy_level FROM departments WHERE parent_department_id IS NULL UNION ALL SELECT d.department_id, d.department_name, d.parent_department_id, d.budget, h.hierarchy_level + 1 FROM departments d INNER JOIN hierarchy h ON d.parent_department_id = h.department_id), subtree AS (SELECT department_id AS root_id, department_id AS desc_id, budget FROM departments UNION ALL SELECT s.root_id, d.department_id, d.budget FROM departments d INNER JOIN subtree s ON d.parent_department_id = s.desc_id) SELECT h.department_id, h.department_name, h.parent_department_id, h.budget, (SELECT SUM(budget) FROM subtree WHERE root_id = h.department_id) AS cumulative_budget, h.hierarchy_level FROM hierarchy h ORDER BY h.department_id;Why this works
Budget/headcount rollups are the #1 reporting use case for recursive CTEs. The pattern is two CTEs: one for hierarchy levels, one for (root, descendant) pairs. Then a correlated subquery SUMs budgets across each subtree. Modern alternative: aggregate inside the recursive step with a 'sum so far' column.
Success check
Every department appears once with an exact subtree budget and correct level.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| department_id | department_name | parent_department_id | budget | cumulative_budget | hierarchy_level |
|---|---|---|---|---|---|
| 1 | Corporate | NULL | 1000000 | 3800000 | 1 |
| 2 | Sales | 1 | 500000 | 950000 | 2 |
| 3 | Engineering | 1 | 800000 | 1550000 | 2 |
| 4 | HR | 1 | 300000 | 300000 | 2 |
| 5 | Sales West | 2 | 200000 | 200000 | 3 |
| 6 | Sales East | 2 | 250000 | 250000 | 3 |
| 7 | Backend Eng | 3 | 400000 | 400000 | 3 |
| 8 | Frontend Eng | 3 | 350000 | 350000 | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.