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
| Column | Type |
|---|---|
| vm_id | int |
| vm_name | varchar |
| environment | enum(prod, dev, test) |
| power_state | enum(running, stopped, deallocated) |
| auto_shutdown | bool |
| hourly_cost | decimal |
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_id | vm_name | environment | power_state | auto_shutdown | hourly_cost |
|---|---|---|---|---|---|
| 1 | vm-dev-build-01 | dev | running | 0 | 0.192 |
| 2 | vm-dev-sql-02 | dev | stopped | 0 | 0.384 |
| 3 | vm-test-web-03 | test | deallocated | 0 | 0.096 |
| 4 | vm-prod-api-04 | prod | running | 0 | 0.768 |
| 5 | vm-test-ml-05 | test | running | 1 | 0.768 |
| 6 | vm-dev-agent-06 | dev | running | 0 | 0.384 |
| 7 | vm-test-load-07 | test | running | 0 | 0.208 |
Output
| vm_name | environment | weekend_cost |
|---|---|---|
| vm-dev-agent-06 | dev | 23.04 |
| vm-dev-sql-02 | dev | 23.04 |
| vm-test-load-07 | test | 12.48 |
| vm-dev-build-01 | dev | 11.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_nameAnother 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 →