Hierarchical Budget Reports: Rolling Up Expenses and Ranking Siblings

The Backstory

A while back, I was working on a network monitoring system. We had routers organized into groups, and groups could be nested. A router might belong to DC1, DC1 belonged to Region-West, Region-West belonged to USA, and so on.

The boss wanted a traffic report that showed:

Total traffic rolled up through the hierarchy (region includes all its DCs and routers)
Sorted by traffic volume (busiest regions first)
Within each parent, children ranked by their traffic
So you could see "Region-West is #1, and within it, DC1 is #1, DC2 is #2..."

I solved it with recursive CTEs and window functions. Then I realized the exact same pattern shows up everywhere in business.

Today I'll show you how to build this for corporate budget reports. Same SQL, different domain.

The Problem

You're tracking departmental budgets. Departments are organized hierarchically:

Company has divisions (Engineering, Finance, Sales)
Divisions have departments (Backend Team, Frontend Team, DevOps)
Departments might have sub-teams

Each leaf node (actual team) has a budget. Parent nodes aggregate their children's budgets.

You need to report:

Total spend for each node (including all descendants)
Ranked by total spend (highest first)
Within each parent, siblings ranked among themselves
Show the full hierarchy

Engineering (Total: $3.8M) - Rank #1 globally
  ├─ Backend Team ($1.5M) - Rank #1 within Engineering
  ├─ Frontend Team ($1.2M) - Rank #2 within Engineering
  └─ DevOps ($1.1M) - Rank #3 within Engineering
Finance (Total: $2.5M) - Rank #2 globally
  ├─ Treasury ($1.2M) - Rank #1 within Finance
  ├─ Accounting ($800K) - Rank #2 within Finance
  └─ Audit ($500K) - Rank #3 within Finance

Let's build this.

The Schema

Simple parent-child hierarchy:

DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  parent_id INTEGER REFERENCES departments(id),
  own_budget NUMERIC(12,2) DEFAULT 0,  -- Direct budget (0 for parent nodes)
  active BOOLEAN DEFAULT true
);

-- Sample data: Company budget hierarchy
INSERT INTO departments (id, dept_name, parent_id, own_budget) VALUES
  -- Top-level divisions (no parent)
  (1, 'Engineering', NULL, 0),
  (2, 'Finance', NULL, 0),
  (3, 'Sales', NULL, 0),
  
  -- Engineering teams
  (4, 'Backend Team', 1, 1500000),
  (5, 'Frontend Team', 1, 1200000),
  (6, 'DevOps', 1, 1100000),
  (7, 'Mobile Team', 1, 800000),
  
  -- Finance departments
  (8, 'Treasury', 2, 0),           -- Has sub-teams
  (9, 'Accounting', 2, 0),          -- Has sub-teams
  (10, 'Audit', 2, 500000),
  
  -- Finance sub-teams
  (11, 'Investment Management', 8, 900000),
  (12, 'Cash Management', 8, 300000),
  (13, 'Payroll', 9, 500000),
  (14, 'Tax Compliance', 9, 300000),
  
  -- Sales regions
  (15, 'West Coast Sales', 3, 0),
  (16, 'East Coast Sales', 3, 0),
  
  -- Sales teams
  (17, 'SF Enterprise', 15, 600000),
  (18, 'LA SMB', 15, 400000),
  (19, 'NYC Enterprise', 16, 800000),
  (20, 'Boston SMB', 16, 350000);

-- Index for performance
CREATE INDEX idx_departments_parent ON departments(parent_id);

The Query: Hierarchical Rollup with Ranking

Here's where it all comes together. Three CTEs:

Traverse the tree with depth and path tracking
Calculate total budget (own + all descendants) using path
Rank within parent using window functions

WITH RECURSIVE
-- Step 1: Traverse hierarchy with depth and path (top-down)
hierarchy AS (
  -- Base case: root nodes (no parent)
  SELECT
    d.id,
    d.dept_name,
    d.parent_id,
    d.own_budget,
    1 AS level,
    ARRAY[d.id] AS path
  FROM departments d
  WHERE d.parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT
    d.id,
    d.dept_name,
    d.parent_id,
    d.own_budget,
    h.level + 1,
    h.path || d.id
  FROM departments d
  JOIN hierarchy h ON d.parent_id = h.id
),

-- Step 2: Calculate total budget (own + all descendants) using path
budget_totals AS (
  SELECT
    h.id,
    h.dept_name,
    h.parent_id,
    h.own_budget,
    h.level,
    h.path,
    (
      SELECT SUM(h2.own_budget)
      FROM hierarchy h2
      WHERE h.id = ANY(h2.path)  -- h2 is h or a descendant of h
    ) AS total_budget
  FROM hierarchy h
),

-- Step 3: Add ranking within parent
ranked_hierarchy AS (
  SELECT
    bt.*,
    ROW_NUMBER() OVER (
      PARTITION BY bt.parent_id
      ORDER BY bt.total_budget DESC, bt.dept_name
    ) AS rank_in_parent,
    -- Global rank for top-level departments
    CASE
      WHEN bt.parent_id IS NULL THEN
        ROW_NUMBER() OVER (ORDER BY bt.total_budget DESC, bt.dept_name)
      ELSE NULL
    END AS global_rank
  FROM budget_totals bt
)

-- Final output
SELECT
  rh.dept_name,
  (SELECT dept_name FROM departments WHERE id = rh.parent_id) AS parent_name,
  rh.level,
  rh.rank_in_parent,
  rh.global_rank,
  '$' || TO_CHAR(rh.total_budget, 'FM999,999,999') AS total_budget,
  '$' || TO_CHAR(rh.own_budget, 'FM999,999,999') AS own_budget,
  -- Add percentage of parent's budget
  CASE
    WHEN rh.parent_id IS NOT NULL THEN
      ROUND(100.0 * rh.total_budget /
        (SELECT total_budget FROM budget_totals WHERE id = rh.parent_id), 1) || '%'
    ELSE NULL
  END AS pct_of_parent
FROM ranked_hierarchy rh
ORDER BY
  rh.path;  -- This maintains hierarchical order

Output:

dept_name              | parent_name        | level | rank_in_parent | global_rank | total_budget | own_budget | pct_of_parent
-----------------------|--------------------|-------|----------------|-------------|--------------|------------|---------------
Engineering            | NULL               | 1     | 1              | 1           | $4,600,000   | $0         | NULL
Backend Team           | Engineering        | 2     | 1              | NULL        | $1,500,000   | $1,500,000 | 32.6%
Frontend Team          | Engineering        | 2     | 2              | NULL        | $1,200,000   | $1,200,000 | 26.1%
DevOps                 | Engineering        | 2     | 3              | NULL        | $1,100,000   | $1,100,000 | 23.9%
Mobile Team            | Engineering        | 2     | 4              | NULL        | $800,000     | $800,000   | 17.4%
Finance                | NULL               | 1     | 2              | 2           | $2,500,000   | $0         | NULL
Treasury               | Finance            | 2     | 1              | NULL        | $1,200,000   | $0         | 48.0%
Investment Management  | Treasury           | 3     | 1              | NULL        | $900,000     | $900,000   | 75.0%
Cash Management        | Treasury           | 3     | 2              | NULL        | $300,000     | $300,000   | 25.0%
Accounting             | Finance            | 2     | 2              | NULL        | $800,000     | $0         | 32.0%
Payroll                | Accounting         | 3     | 1              | NULL        | $500,000     | $500,000   | 62.5%
Tax Compliance         | Accounting         | 3     | 2              | NULL        | $300,000     | $300,000   | 37.5%
Audit                  | Finance            | 2     | 3              | NULL        | $500,000     | $500,000   | 20.0%
Sales                  | NULL               | 1     | 3              | 3           | $2,150,000   | $0         | NULL
West Coast Sales       | Sales              | 2     | 1              | NULL        | $1,000,000   | $0         | 46.5%
SF Enterprise          | West Coast Sales   | 3     | 1              | NULL        | $600,000     | $600,000   | 60.0%
LA SMB                 | West Coast Sales   | 3     | 2              | NULL        | $400,000     | $400,000   | 40.0%
East Coast Sales       | Sales              | 2     | 2              | NULL        | $1,150,000   | $0         | 53.5%
NYC Enterprise         | East Coast Sales   | 3     | 1              | NULL        | $800,000     | $800,000   | 69.6%
Boston SMB             | East Coast Sales   | 3     | 2              | NULL        | $350,000     | $350,000   | 30.4%

What this tells you:

Engineering is #1 globally ($4.6M total)
Within Engineering, Backend Team is #1 ($1.5M, 32.6% of Engineering's budget)
Finance is #2 globally ($2.5M total)
Within Finance, Treasury is #1 ($1.2M, 48% of Finance's budget)
Within Treasury, Investment Management is #1 ($900K, 75% of Treasury's budget)

Perfect for executive dashboards and budget reviews.

Breaking Down the Query

Step 1: Hierarchy Traversal (Top-Down)

First, traverse from root to leaves, tracking depth and building a path array:

hierarchy AS (
  -- Root nodes (no parent)
  SELECT id, dept_name, parent_id, own_budget, 1 AS level, ARRAY[id] AS path
  FROM departments
  WHERE parent_id IS NULL

  UNION ALL

  -- Children
  SELECT d.id, d.dept_name, d.parent_id, d.own_budget, h.level + 1, h.path || d.id
  FROM departments d
  JOIN hierarchy h ON d.parent_id = h.id
)

The path array is key: It tracks the full ancestry. Engineering's path is {1}, Backend Team's is {1,4}. This lets us sort hierarchically AND calculate totals.

Step 2: Budget Totals (Using Path for Aggregation)

Now calculate total budget for each node using the path array:

budget_totals AS (
  SELECT
    h.id,
    h.dept_name,
    h.own_budget,
    (
      SELECT SUM(h2.own_budget)
      FROM hierarchy h2
      WHERE h.id = ANY(h2.path)  -- h2 is h or a descendant of h
    ) AS total_budget
  FROM hierarchy h
)

How it works:

For each node, we sum the own_budget of all nodes whose path contains that node's id
If node X is in node Y's path, then Y is either X itself or a descendant of X
Example: Treasury (id=8) appears in paths of: Treasury {2,8}, Investment Management {2,8,11}, Cash Management {2,8,12}
So Treasury's total = $0 + $900K + $300K = $1.2M

Step 3: Ranking Within Parent

Use window functions to rank siblings:

ROW_NUMBER() OVER (
  PARTITION BY parent_id 
  ORDER BY total_budget DESC
) AS rank_in_parent

This means:

All children of Engineering (parent_id = 1) are ranked among themselves
All children of Treasury (parent_id = 8) are ranked among themselves
Separate rankings per parent

Alternate Sorting Strategies

Want top spenders first at each level?

ORDER BY 
  COALESCE(rh.global_rank, 999),  -- Top-level depts by rank
  rh.parent_id,                    -- Group by parent
  rh.rank_in_parent;               -- Then rank within parent

Want alphabetical within each parent?

ORDER BY rh.path;  -- Hierarchical, then alphabetical

Want to show only departments over $1M?

Add a WHERE clause:

WHERE rh.total_budget >= 1000000

Challenge 1: Find Top N Spenders Per Division

Goal: Find the top 3 budget consumers within each top-level division (Engineering, Finance, Sales).

Expected output:

division_name | dept_name             | total_budget | rank
--------------|-----------------------|--------------|------
Engineering   | Backend Team          | $1,500,000   | 1
Engineering   | Frontend Team         | $1,200,000   | 2
Engineering   | DevOps                | $1,100,000   | 3
Finance       | Treasury              | $1,200,000   | 1
Finance       | Accounting            | $800,000     | 2
Finance       | Audit                 | $500,000     | 3
Sales         | East Coast Sales      | $1,150,000   | 1
Sales         | West Coast Sales      | $1,000,000   | 2
Sales         | NYC Enterprise        | $800,000     | 3

Hints:

The path array already contains the answer - path[1] is always the top-level division
Add a CTE that extracts the division from each node's path
Use ROW_NUMBER() OVER (PARTITION BY division_id ...) to rank within each division
Filter to rank <= 3 in the final SELECT

Pseudocode:

WITH hierarchy AS (... same as before ...),
     budget_totals AS (... same as before ...),

     with_division AS (
       -- For each node, extract division_id = path[1]
       -- Exclude level 1 nodes (they ARE divisions)
     ),

     ranked AS (
       -- ROW_NUMBER partitioned by division, ordered by total_budget DESC
     )

SELECT * FROM ranked WHERE rank <= 3

Challenge 2: Budget Variance Report

Goal: Compare actual spending to budgeted amounts, with variance rolled up through the hierarchy.

Setup: Add an actual_spend column to track real expenses:

ALTER TABLE departments ADD COLUMN actual_spend NUMERIC(12,2) DEFAULT 0;

-- Sample data: most teams under budget, a few over
UPDATE departments SET actual_spend = own_budget * 0.95 WHERE own_budget > 0;
UPDATE departments SET actual_spend = own_budget * 1.10 WHERE id IN (4, 11);

Expected output columns:

dept_name, level
budgeted (total budget rolled up)
actual (total actual spend rolled up)
variance (actual - budgeted)
variance_pct (percentage over/under)
status (Over budget / Under budget / On track)

Hints:

You already know how to roll up own_budget using the path array
Apply the exact same pattern to roll up actual_spend
Join both totals together and calculate variance
Use a CASE expression for the status indicator

Pseudocode:

WITH hierarchy AS (... same as before ...),

     budget_totals AS (
       -- SUM own_budget for each node and descendants
     ),

     actual_totals AS (
       -- SUM actual_spend for each node and descendants (same pattern!)
     )

SELECT
  dept_name,
  total_budget AS budgeted,
  total_actual AS actual,
  (total_actual - total_budget) AS variance,
  -- Calculate percentage and status
FROM hierarchy
JOIN budget_totals ...
JOIN actual_totals ...

These challenges let you practice the pattern. Solutions use the same core technique - the path-based aggregation.

The Pattern (Again)

Whether it's:

Network topology: routers → groups → regions (sorted by traffic)
Corporate budgets: teams → departments → divisions (sorted by spend)
Sales territories: reps → offices → regions (sorted by revenue)
Product catalog: SKUs → subcategories → categories (sorted by sales)

The SQL pattern is always:

WITH RECURSIVE
-- 1. Traverse top-down with path tracking
hierarchy AS (
  SELECT id, parent_id, metric, 1 AS level, ARRAY[id] AS path
  FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, n.metric, h.level + 1, h.path || n.id
  FROM nodes n
  JOIN hierarchy h ON n.parent_id = h.id
),

-- 2. Calculate aggregates using path
aggregates AS (
  SELECT
    h.id,
    h.parent_id,
    h.level,
    h.path,
    (SELECT SUM(h2.metric) FROM hierarchy h2 WHERE h.id = ANY(h2.path)) AS total_metric
  FROM hierarchy h
),

-- 3. Rank within parent
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY total_metric DESC) AS rank
  FROM aggregates
)

SELECT * FROM ranked ORDER BY path;

Same structure, different metrics.

Real-World Applications

I've used this exact pattern for:

Network capacity planning (the original router groups problem)
Department budgeting (this example)
Sales pipeline analysis (deals → accounts → territories → regions)
Inventory management (SKUs → shelves → aisles → warehouses)
Website analytics (pages → sections → categories, sorted by traffic)
Project cost tracking (tasks → workstreams → projects → programs)

The recursive aggregation + hierarchical ranking combo is incredibly powerful.

Performance Tips

For large hierarchies (1000+ nodes):

Materialize the aggregates if they don't change often:

CREATE MATERIALIZED VIEW dept_budget_rollup AS
WITH RECURSIVE budget_totals AS (...) SELECT * FROM budget_totals;

REFRESH MATERIALIZED VIEW dept_budget_rollup;

Index the parent_id (we already did):

CREATE INDEX idx_departments_parent ON departments(parent_id);

Limit depth if you know max levels:

WHERE level <= 5

Cache the path array in a separate table for faster sorting:

CREATE TABLE dept_paths AS
WITH RECURSIVE hierarchy AS (...) SELECT id, path FROM hierarchy;

Try It Yourself

Switch to editor tab, fork this book to create a PostgreSQL sandbox and try it on your own..

Challenges:

Add a region column and rank departments within their region
Find departments that are more than 20% over budget
Calculate the average budget per level in the hierarchy
Identify departments where children's budgets don't sum to parent's budget (data quality check)
Show only the path from root to the highest-spending leaf node

Advanced:

What if a department can have multiple parents? (Matrix org structure)
How would you handle budget changes over time? (Add effective_date)
Can you generate a CSV export with proper indentation for Excel?

Share your solutions!

Tags: #SQL #RecursiveCTE #Hierarchical #Budgets #Ranking #WindowFunctions

Difficulty: Advanced
Topics: Recursive CTEs, Window Functions, Aggregation, Hierarchical Data