Microsoft: Azure Subscription Budget Status This Month

Easy
780.

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.

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

ColumnType
subscription_idint
display_namevarchar
monthly_budgetint
month_to_date_costdecimal

Primary key: subscription_id.

One row per subscription. monthly_budget is NULL when no budget has been set; amounts are in US dollars.

Example

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

AzureSubscription

subscription_iddisplay_namemonthly_budgetmonth_to_date_cost
1Contoso Prod50006200.5
2Contoso Dev1000800
3Contoso Test1000799.75
4Data Science SandboxNULL350.25
5Shared Networking20002000
6Marketing Site1500120

Output

display_namebudget_status
Contoso ProdOver budget
Contoso DevAt risk
Contoso TestOn track
Data Science SandboxNo budget
Shared NetworkingAt risk
Marketing SiteOn track
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 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

ColumnType
subscription_idint
display_namevarchar
monthly_budgetint
month_to_date_costdecimal

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_iddisplay_namemonthly_budgetmonth_to_date_cost
1Contoso Prod50006200.5
2Contoso Dev1000800
3Contoso Test1000799.75
4Data Science SandboxNULL350.25
5Shared Networking20002000
6Marketing Site1500120

Output

display_namebudget_status
Contoso ProdOver budget
Contoso DevAt risk
Contoso TestOn track
Data Science SandboxNo budget
Shared NetworkingAt risk
Marketing SiteOn 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 AzureSubscription

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