Microsoft: Azure Resource Count per Resource Group From Resource IDs

Easy
778.

Microsoft: Azure Resource Count per Resource Group From Resource IDs

An Azure governance engineer has an inventory export with only the full resource ID of each resource, and wants to know how many resources live in each resource group.

A resource ID looks like /subscriptions/<subscription>/resourceGroups/<group>/providers/<namespace>/<type>/<name>. Return subscription_id, resource_group and resources (how many resources the group holds). The same group name in two subscriptions is two different groups. 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: AzureResource

ColumnType
resource_idvarchar
locationvarchar
created_ondate

Primary key: resource_id.

One row per resource. Every resource_id follows the format above, with resourceGroups always spelled exactly that way.

Example

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

AzureResource

resource_idlocationcreated_on
/subscriptions/sub-prod-01/resourceGroups/rg-web/providers/Microsoft.Web/sites/app-storecentralindia2025-01-12
/subscriptions/sub-prod-01/resourceGroups/rg-web/providers/Microsoft.Network/frontDoors/fd-storeglobal2025-01-12
/subscriptions/sub-prod-01/resourceGroups/rg-data/providers/Microsoft.Sql/servers/sql-orderscentralindia2025-01-20
/subscriptions/sub-dev-02/resourceGroups/rg-web/providers/Microsoft.Web/sites/app-store-devsouthindia2025-02-02
/subscriptions/sub-dev-02/resourceGroups/rg-web/providers/Microsoft.Storage/storageAccounts/stdevassetssouthindia2025-02-03
/subscriptions/sub-dev-02/resourceGroups/rg-ml/providers/Microsoft.Compute/virtualMachines/vm-gpu-01eastus2025-02-10

Output

subscription_idresource_groupresources
sub-dev-02rg-ml1
sub-dev-02rg-web2
sub-prod-01rg-data1
sub-prod-01rg-web2
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: Azure Resource Count per Resource Group From Resource IDs

An Azure governance engineer has an inventory export with only the full resource ID of each resource, and wants to know how many resources live in each…

  • Difficulty: Easy
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #778

Problem statement

An Azure governance engineer has an inventory export with only the full resource ID of each resource, and wants to know how many resources live in each resource group.

A resource ID looks like /subscriptions/<subscription>/resourceGroups/<group>/providers/<namespace>/<type>/<name>. Return subscription_id, resource_group and resources (how many resources the group holds). The same group name in two subscriptions is two different groups. 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: AzureResource

ColumnType
resource_idvarchar
locationvarchar
created_ondate

Primary key: resource_id.

One row per resource. Every resource_id follows the format above, with resourceGroups always spelled exactly that way.

Examples

Example 1

AzureResource

resource_idlocationcreated_on
/subscriptions/sub-prod-01/resourceGroups/rg-web/providers/Microsoft.Web/sites/app-storecentralindia2025-01-12
/subscriptions/sub-prod-01/resourceGroups/rg-web/providers/Microsoft.Network/frontDoors/fd-storeglobal2025-01-12
/subscriptions/sub-prod-01/resourceGroups/rg-data/providers/Microsoft.Sql/servers/sql-orderscentralindia2025-01-20
/subscriptions/sub-dev-02/resourceGroups/rg-web/providers/Microsoft.Web/sites/app-store-devsouthindia2025-02-02
/subscriptions/sub-dev-02/resourceGroups/rg-web/providers/Microsoft.Storage/storageAccounts/stdevassetssouthindia2025-02-03
/subscriptions/sub-dev-02/resourceGroups/rg-ml/providers/Microsoft.Compute/virtualMachines/vm-gpu-01eastus2025-02-10

Output

subscription_idresource_groupresources
sub-dev-02rg-ml1
sub-dev-02rg-web2
sub-prod-01rg-data1
sub-prod-01rg-web2

How to solve Microsoft: Azure Resource Count per Resource Group From Resource IDs

The resource ID is a path with a fixed shape, so the subscription and the resource group sit at fixed positions: the string starts with a slash, so the segments are '', subscriptions, the subscription, resourceGroups, the group. SUBSTRING_INDEX(resource_id, '/', 3) keeps everything up to the third slash — /subscriptions/sub-prod-01 — and SUBSTRING_INDEX(…, '/', -1) takes its last segment. The same trick with 5 gives the group.

Once both are columns, it is an ordinary GROUP BY with COUNT(*). Grouping by the pair matters: resource-group names are only unique inside a subscription, so rg-web in production and in dev are different groups.

The alternative anchors on the literal markers instead of counting slashes: take what follows /resourceGroups/ up to the next slash. It is more robust if an ID ever had an extra leading segment. Both are one scan with string work per row.

Reference solution (MySQL)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(resource_id, '/', 3), '/', -1) AS subscription_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(resource_id, '/', 5), '/', -1) AS resource_group,
       COUNT(*) AS resources
FROM AzureResource
GROUP BY subscription_id, resource_group

Another way

SELECT sub AS subscription_id, rg AS resource_group, COUNT(*) AS resources
FROM (
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(resource_id, '/subscriptions/', -1), '/', 1) AS sub,
         SUBSTRING_INDEX(SUBSTRING_INDEX(resource_id, '/resourceGroups/', -1), '/', 1) AS rg
  FROM AzureResource
) p
GROUP BY sub, rg

← Microsoft: Outlook Mailboxes at 90% of Their Quota or More · Microsoft: Products With Three or More Severity A Cases in March →