Microsoft: Azure Q1 Cost Split by Service Family per Month
An Azure FinOps lead wants one row per month of Q1 2025 showing where the cloud bill went, split into three service families.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #781
Problem statement
An Azure FinOps lead wants one row per month of Q1 2025 showing where the cloud bill went, split into three service families.
Map each usage record's meter_category to a family: compute = Virtual Machines or Azure Kubernetes Service; data = Storage or SQL Database; other = every other category. For each month of January–March 2025 that has any usage, return usage_month (as 'YYYY-MM'), compute_cost, data_cost, other_cost and total_cost, each rounded to 2 decimals (0 when the family has no usage that month). Order by usage_month.
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: AzureUsage
| Column | Type |
|---|---|
| usage_id | int |
| subscription_id | varchar |
| usage_date | date |
| meter_category | varchar |
| cost | decimal |
Primary key: usage_id.
One row per daily usage record from the cost export; cost is in US dollars.
Examples
Example 1
AzureUsage
| usage_id | subscription_id | usage_date | meter_category | cost |
|---|---|---|---|---|
| 1 | sub-prod-01 | 2024-12-31 | Virtual Machines | 410 |
| 2 | sub-prod-01 | 2025-01-01 | Virtual Machines | 120.5 |
| 3 | sub-prod-01 | 2025-01-15 | Storage | 35.25 |
| 4 | sub-dev-02 | 2025-01-31 | Bandwidth | 12.4 |
| 5 | sub-dev-02 | 2025-02-10 | Azure Kubernetes Service | 260 |
| 6 | sub-prod-01 | 2025-02-28 | Virtual Machines | 99.99 |
| 7 | sub-prod-01 | 2025-03-03 | SQL Database | 150 |
| 8 | sub-dev-02 | 2025-03-20 | Azure Monitor | 8.75 |
| 9 | sub-prod-01 | 2025-04-01 | Storage | 44 |
Output
| usage_month | compute_cost | data_cost | other_cost | total_cost |
|---|---|---|---|---|
| 2025-01 | 120.5 | 35.25 | 12.4 | 168.15 |
| 2025-02 | 359.99 | 0 | 0 | 359.99 |
| 2025-03 | 0 | 150 | 8.75 | 158.75 |
How to solve Microsoft: Azure Q1 Cost Split by Service Family per Month
This is a pivot: the rows are months and the columns are families. Group by the month label (DATE_FORMAT(usage_date, '%Y-%m')), and turn each family into a column with conditional aggregation — SUM(CASE WHEN <category is in the family> THEN cost ELSE 0 END). The ELSE 0 matters: it makes a family with no usage in a month show 0 instead of NULL, as the statement asks.
The "other" bucket is the complement of the two named families, written with NOT IN; meter_category is never NULL, so the complement is exact and the three columns always add up to the total. The quarter filter runs in WHERE, before grouping, so a month outside Q1 never forms a group, and a month with no usage at all simply does not appear.
The alternative maps each record to a family in a derived table first (with a simple CASE) and pivots on the family name with IF — handy when the mapping is reused. Both are one scan.
Reference solution (MySQL)
SELECT DATE_FORMAT(usage_date, '%Y-%m') AS usage_month,
ROUND(SUM(CASE WHEN meter_category IN ('Virtual Machines', 'Azure Kubernetes Service') THEN cost ELSE 0 END), 2) AS compute_cost,
ROUND(SUM(CASE WHEN meter_category IN ('Storage', 'SQL Database') THEN cost ELSE 0 END), 2) AS data_cost,
ROUND(SUM(CASE WHEN meter_category NOT IN ('Virtual Machines', 'Azure Kubernetes Service', 'Storage', 'SQL Database') THEN cost ELSE 0 END), 2) AS other_cost,
ROUND(SUM(cost), 2) AS total_cost
FROM AzureUsage
WHERE usage_date >= '2025-01-01' AND usage_date < '2025-04-01'
GROUP BY usage_month
ORDER BY usage_monthAnother way
SELECT usage_month,
ROUND(SUM(IF(family = 'compute', cost, 0)), 2) AS compute_cost,
ROUND(SUM(IF(family = 'data', cost, 0)), 2) AS data_cost,
ROUND(SUM(IF(family = 'other', cost, 0)), 2) AS other_cost,
ROUND(SUM(cost), 2) AS total_cost
FROM (
SELECT LEFT(usage_date, 7) AS usage_month, cost,
CASE meter_category WHEN 'Virtual Machines' THEN 'compute' WHEN 'Azure Kubernetes Service' THEN 'compute'
WHEN 'Storage' THEN 'data' WHEN 'SQL Database' THEN 'data' ELSE 'other' END AS family
FROM AzureUsage
WHERE YEAR(usage_date) = 2025 AND QUARTER(usage_date) = 1
) u
GROUP BY usage_month
ORDER BY usage_month← Microsoft: Azure Subscription Budget Status This Month · Microsoft: Teams Channels Where Guests Post Most Messages →