Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget

Hard
793.

Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget

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

ColumnType
group_idint
group_namevarchar
parent_idint
monthly_budgetint

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

ColumnType
subscription_idint
group_idint
display_namevarchar

Primary key: subscription_id.

Every subscription sits directly in exactly one management group.

Table: MonthlyCost

ColumnType
subscription_idint
cost_monthchar
costint

Primary key (together): subscription_id, cost_month.

A subscription's billed cost for a month ('YYYY-MM'), in US dollars.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

ManagementGroup

group_idgroup_nameparent_idmonthly_budget
1Contoso RootNULL20000
2Platform18000
3Landing Zones19000
4Corp34000
5Online3NULL
6Sandbox1500

Subscription

subscription_idgroup_iddisplay_name
1012connectivity
1022identity
1034corp-erp
1044corp-hr
1055online-store
1066sandbox-ml

MonthlyCost

subscription_idcost_monthcost
1012025-053000
1022025-052500
1032025-049900
1032025-052600
1042025-051900
1052025-055000
1062025-04900
1062025-05500

Output

group_namerolled_up_costmonthly_budget
Landing Zones95009000
Corp45004000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
group_idint
group_namevarchar
parent_idint
monthly_budgetint

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

ColumnType
subscription_idint
group_idint
display_namevarchar

Primary key: subscription_id.

Every subscription sits directly in exactly one management group.

Table: MonthlyCost

ColumnType
subscription_idint
cost_monthchar
costint

Primary key: subscription_id, cost_month.

A subscription's billed cost for a month ('YYYY-MM'), in US dollars.

Examples

Example 1

ManagementGroup

group_idgroup_nameparent_idmonthly_budget
1Contoso RootNULL20000
2Platform18000
3Landing Zones19000
4Corp34000
5Online3NULL
6Sandbox1500

Subscription

subscription_idgroup_iddisplay_name
1012connectivity
1022identity
1034corp-erp
1044corp-hr
1055online-store
1066sandbox-ml

MonthlyCost

subscription_idcost_monthcost
1012025-053000
1022025-052500
1032025-049900
1032025-052600
1042025-051900
1052025-055000
1062025-04900
1062025-05500

Output

group_namerolled_up_costmonthly_budget
Landing Zones95009000
Corp45004000

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_budget

Another 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 →