Microsoft: Dev and Test VMs Still Billing Over the Weekend

Easy
773.

Microsoft: Dev and Test VMs Still Billing Over the Weekend

EasyBasics

MySQL

An Azure FinOps analyst runs a Friday-evening check for non-production virtual machines that will keep costing money until Monday morning — a 60-hour weekend.

A VM keeps billing for compute while it is running, and also while it is stopped from inside the guest OS: only a deallocated VM stops billing. Return every VM in the dev or test environment that is still billing and has no auto-shutdown schedule (auto_shutdown = 0). Return vm_name, environment and weekend_cost (hourly_cost × 60, rounded to 2 decimals), ordered by weekend_cost descending, then vm_name ascending.

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: VirtualMachine

ColumnTypeValues
vm_idint
vm_namevarchar
environmentenumprod, dev, test
power_stateenumrunning, stopped, deallocated
auto_shutdownbool
hourly_costdecimal

Primary key: vm_id.

One row per VM as of Friday 20:00. hourly_cost is the pay-as-you-go compute rate in US dollars; vm_name is unique.

Example

The rows must come back in the order shown.

VirtualMachine

vm_idvm_nameenvironmentpower_stateauto_shutdownhourly_cost
1vm-dev-build-01devrunning00.192
2vm-dev-sql-02devstopped00.384
3vm-test-web-03testdeallocated00.096
4vm-prod-api-04prodrunning00.768
5vm-test-ml-05testrunning10.768
6vm-dev-agent-06devrunning00.384
7vm-test-load-07testrunning00.208

Output

vm_nameenvironmentweekend_cost
vm-dev-agent-06dev23.04
vm-dev-sql-02dev23.04
vm-test-load-07test12.48
vm-dev-build-01dev11.52
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: Dev and Test VMs Still Billing Over the Weekend

An Azure FinOps analyst runs a Friday-evening check for non-production virtual machines that will keep costing money until Monday morning — a 60-hour weekend.

  • Difficulty: Easy
  • Topics: Basics
  • Dialect: MySQL
  • Problem: #773

Problem statement

An Azure FinOps analyst runs a Friday-evening check for non-production virtual machines that will keep costing money until Monday morning — a 60-hour weekend.

A VM keeps billing for compute while it is running, and also while it is stopped from inside the guest OS: only a deallocated VM stops billing. Return every VM in the dev or test environment that is still billing and has no auto-shutdown schedule (auto_shutdown = 0). Return vm_name, environment and weekend_cost (hourly_cost × 60, rounded to 2 decimals), ordered by weekend_cost descending, then vm_name ascending.

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: VirtualMachine

ColumnType
vm_idint
vm_namevarchar
environmentenum(prod, dev, test)
power_stateenum(running, stopped, deallocated)
auto_shutdownbool
hourly_costdecimal

Primary key: vm_id.

One row per VM as of Friday 20:00. hourly_cost is the pay-as-you-go compute rate in US dollars; vm_name is unique.

Examples

Example 1

VirtualMachine

vm_idvm_nameenvironmentpower_stateauto_shutdownhourly_cost
1vm-dev-build-01devrunning00.192
2vm-dev-sql-02devstopped00.384
3vm-test-web-03testdeallocated00.096
4vm-prod-api-04prodrunning00.768
5vm-test-ml-05testrunning10.768
6vm-dev-agent-06devrunning00.384
7vm-test-load-07testrunning00.208

Output

vm_nameenvironmentweekend_cost
vm-dev-agent-06dev23.04
vm-dev-sql-02dev23.04
vm-test-load-07test12.48
vm-dev-build-01dev11.52

How to solve Microsoft: Dev and Test VMs Still Billing Over the Weekend

This is a filter plus a computed column. A VM belongs on the list when it is non-production (environment IN ('dev', 'test')), still billing, and has no auto-shutdown. The trap is the power state: Azure keeps charging for compute while a VM is merely stopped from inside the guest, so the billing states are running and stopped; only deallocated is safe. Writing power_state = 'running' would miss exactly the VMs people think they turned off.

The weekend cost is the hourly rate times 60 hours, rounded to cents. The sort is fully specified — cost descending, then name — so two VMs with the same rate come out in a fixed order and the answer is deterministic.

The alternative writes each condition as its negation (<> 'prod', <> 'deallocated', NOT auto_shutdown), which is equivalent because every column is an enum or a flag with no NULLs. It is a single scan of the table either way.

Reference solution (MySQL)

SELECT vm_name, environment, ROUND(hourly_cost * 60, 2) AS weekend_cost
FROM VirtualMachine
WHERE environment IN ('dev', 'test')
  AND power_state IN ('running', 'stopped')
  AND auto_shutdown = 0
ORDER BY weekend_cost DESC, vm_name

Another way

SELECT vm_name, environment, ROUND(60 * hourly_cost, 2) AS weekend_cost FROM VirtualMachine WHERE environment <> 'prod' AND power_state <> 'deallocated' AND NOT auto_shutdown ORDER BY ROUND(60 * hourly_cost, 2) DESC, vm_name ASC

← Meta: Accounts Linked to Banned Accounts Through Shared Devices · Microsoft: Teams Meetings That Ran Past Their Booked Slot →