Microsoft: Azure Subscription Budget Status This Month
A cloud cost-management dashboard shows each Azure subscription's month-to-date spend against the budget its owner set.
- Difficulty: Easy
- Topics: Conditional Logic
- Dialect: MySQL
- Problem: #780
Problem statement
A cloud cost-management dashboard shows each Azure subscription's month-to-date spend against the budget its owner set.
Return display_name and budget_status for every subscription, where the status is: No budget when monthly_budget is NULL; otherwise Over budget when the month-to-date cost is strictly above the budget; At risk when it is at least 80% of the budget; and On track below that. 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: AzureSubscription
| Column | Type |
|---|---|
| subscription_id | int |
| display_name | varchar |
| monthly_budget | int |
| month_to_date_cost | decimal |
Primary key: subscription_id.
One row per subscription. monthly_budget is NULL when no budget has been set; amounts are in US dollars.
Examples
Example 1
AzureSubscription
| subscription_id | display_name | monthly_budget | month_to_date_cost |
|---|---|---|---|
| 1 | Contoso Prod | 5000 | 6200.5 |
| 2 | Contoso Dev | 1000 | 800 |
| 3 | Contoso Test | 1000 | 799.75 |
| 4 | Data Science Sandbox | NULL | 350.25 |
| 5 | Shared Networking | 2000 | 2000 |
| 6 | Marketing Site | 1500 | 120 |
Output
| display_name | budget_status |
|---|---|
| Contoso Prod | Over budget |
| Contoso Dev | At risk |
| Contoso Test | On track |
| Data Science Sandbox | No budget |
| Shared Networking | At risk |
| Marketing Site | On track |
How to solve Microsoft: Azure Subscription Budget Status This Month
A searched CASE evaluates its branches top to bottom and returns the first true one, so precedence is just the order they are written in. The NULL budget must be checked first: month_to_date_cost > NULL is unknown, not false, and without the explicit test a subscription with no budget would slide down to On track — a misleading label for something nobody is watching.
Then over budget (strictly greater), then at risk (at least 80%), and everything else is on track. Spending exactly the budget is At risk, not over. Writing the 80% test as cost * 5 >= budget * 4 avoids a decimal threshold; the alternative's cost / budget >= 0.8 agrees here because 800/1000 rounds to the same double as the literal 0.8.
The alternative nests IF calls, which is MySQL shorthand for the same decision ladder. One scan.
Reference solution (MySQL)
SELECT display_name,
CASE
WHEN monthly_budget IS NULL THEN 'No budget'
WHEN month_to_date_cost > monthly_budget THEN 'Over budget'
WHEN month_to_date_cost * 5 >= monthly_budget * 4 THEN 'At risk'
ELSE 'On track'
END AS budget_status
FROM AzureSubscriptionAnother way
SELECT display_name, IF(monthly_budget IS NULL, 'No budget', IF(month_to_date_cost > monthly_budget, 'Over budget', IF(month_to_date_cost / monthly_budget >= 0.8, 'At risk', 'On track'))) AS budget_status FROM AzureSubscription← Microsoft: Products With Three or More Severity A Cases in March · Microsoft: Azure Q1 Cost Split by Service Family per Month →