Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget
Azure management groups form a tree: each group can hold subscriptions and child groups.
- Difficulty: Hard
- Topics: Subqueries, Joins, Aggregation
- Dialect: MySQL
- Problem: #793
Problem statement
Azure management groups form a tree: each group can hold subscriptions and child groups. A cloud governance lead sets a monthly budget on some groups and wants to know which went over in May 2025 (cost_month = '2025-05').
A group's rolled-up cost is the May cost of every subscription directly in it or in any group below it, at any depth. For each group that has a budget and whose rolled-up cost is strictly greater than it, return group_name, rolled_up_cost and monthly_budget. Return the rows in any order.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: ManagementGroup
| Column | Type |
|---|---|
| group_id | int |
| group_name | varchar |
| parent_id | int |
| monthly_budget | int |
Primary key: group_id.
One row per management group; parent_id is NULL for the root. monthly_budget is NULL when none is set. The hierarchy never loops.
Table: Subscription
| Column | Type |
|---|---|
| subscription_id | int |
| group_id | int |
| display_name | varchar |
Primary key: subscription_id.
Every subscription sits directly in exactly one management group.
Table: MonthlyCost
| Column | Type |
|---|---|
| subscription_id | int |
| cost_month | char |
| cost | int |
Primary key: subscription_id, cost_month.
A subscription's billed cost for a month ('YYYY-MM'), in US dollars.
Examples
Example 1
ManagementGroup
| group_id | group_name | parent_id | monthly_budget |
|---|---|---|---|
| 1 | Contoso Root | NULL | 20000 |
| 2 | Platform | 1 | 8000 |
| 3 | Landing Zones | 1 | 9000 |
| 4 | Corp | 3 | 4000 |
| 5 | Online | 3 | NULL |
| 6 | Sandbox | 1 | 500 |
Subscription
| subscription_id | group_id | display_name |
|---|---|---|
| 101 | 2 | connectivity |
| 102 | 2 | identity |
| 103 | 4 | corp-erp |
| 104 | 4 | corp-hr |
| 105 | 5 | online-store |
| 106 | 6 | sandbox-ml |
MonthlyCost
| subscription_id | cost_month | cost |
|---|---|---|
| 101 | 2025-05 | 3000 |
| 102 | 2025-05 | 2500 |
| 103 | 2025-04 | 9900 |
| 103 | 2025-05 | 2600 |
| 104 | 2025-05 | 1900 |
| 105 | 2025-05 | 5000 |
| 106 | 2025-04 | 900 |
| 106 | 2025-05 | 500 |
Output
| group_name | rolled_up_cost | monthly_budget |
|---|---|---|
| Landing Zones | 9500 | 9000 |
| Corp | 4500 | 4000 |
How to solve Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget
Rolled-up cost needs, for each group, the set of groups beneath it at any depth, and only recursion can produce that without knowing the depth in advance. The recursive CTE builds the closure of the tree: the anchor pairs every group with itself (so a group's own subscriptions count), and each step extends a pair (ancestor, member) to the member's children. Landing Zones ends up paired with itself, Corp and Online.
Separately, May cost is summed per group from its own subscriptions — the April rows are filtered out here. Joining the closure to that, and grouping by ancestor, gives the rolled-up cost: Corp 4,500, Landing Zones 9,500 (Corp plus Online's 5,000, even though Online itself has no budget). Groups without a budget are skipped, and HAVING SUM > budget is strict, so Sandbox at exactly 500 is fine.
The alternative walks upwards: every subscription is expanded into all of its ancestor groups, then costs are grouped by group. It recurses once per subscription rather than once per group, which suits wide trees with few subscriptions.
Reference solution (MySQL)
WITH RECURSIVE tree AS (
SELECT group_id AS ancestor_id, group_id AS member_id FROM ManagementGroup
UNION ALL
SELECT t.ancestor_id, g.group_id
FROM tree t JOIN ManagementGroup g ON g.parent_id = t.member_id
), group_spend AS (
SELECT s.group_id, SUM(c.cost) AS cost
FROM Subscription s
JOIN MonthlyCost c ON c.subscription_id = s.subscription_id
WHERE c.cost_month = '2025-05'
GROUP BY s.group_id
)
SELECT g.group_name, SUM(gs.cost) AS rolled_up_cost, g.monthly_budget
FROM ManagementGroup g
JOIN tree t ON t.ancestor_id = g.group_id
JOIN group_spend gs ON gs.group_id = t.member_id
WHERE g.monthly_budget IS NOT NULL
GROUP BY g.group_id, g.group_name, g.monthly_budget
HAVING SUM(gs.cost) > g.monthly_budgetAnother way
WITH RECURSIVE lineage AS (
SELECT s.subscription_id, s.group_id AS group_id FROM Subscription s
UNION ALL
SELECT l.subscription_id, g.parent_id
FROM lineage l JOIN ManagementGroup g ON g.group_id = l.group_id
WHERE g.parent_id IS NOT NULL
)
SELECT g.group_name, SUM(c.cost) AS rolled_up_cost, g.monthly_budget
FROM lineage l
JOIN MonthlyCost c ON c.subscription_id = l.subscription_id AND c.cost_month = '2025-05'
JOIN ManagementGroup g ON g.group_id = l.group_id
WHERE g.monthly_budget IS NOT NULL
GROUP BY g.group_id, g.group_name, g.monthly_budget
HAVING SUM(c.cost) > g.monthly_budget← Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync · Microsoft: Azure Prepayment Balance and Overage Month by Month →