Back to questions
Spare Server Capacity Microsoft SQL Interview Question
Spare Server Capacity
Microsoft SQL Interview Question
Microsoft Azure's capacity planning team wants to understand how much data its customers are using, and how much spare capacity is left in each of its data centers. You’re given three tables: customers, data centers, and forecasted_demand.
Write a query to find each data centre’s total unused server capacity. Output the data center id in ascending order and the total spare capacity.
Definitions:
- is the total monthly server capacity for each centers.
- is the server demand for each customer.
P.S. If you've read the Ace the Data Science Interview and liked it, consider writing us a review?
Table:
| Column Name | Type |
|---|---|
| datacenter_id | integer |
| name | string |
| monthly_capacity | integer |
Example Input:
| datacenter_id | name | monthly_capacity |
|---|---|---|
| 1 | London | 100 |
| 3 | Amsterdam | 250 |
| 4 | Hong Kong | 400 |
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| datacenter_id | integer |
| monthly_demand | integer |
Example Input:
| customer_id | datacenter_id | monthly_demand |
|---|---|---|
| 109 | 4 | 120 |
| 144 | 3 | 60 |
| 144 | 4 | 105 |
| 852 | 1 | 60 |
| 852 | 3 | 178 |
Example Output:
| datacenter_id | spare_capacity |
|---|---|
| 1 | 40 |
| 3 | 12 |
| 4 | 175 |
The dataset you are querying against may have different input & output - this is just an example!
Solution
Objective: Find each data centre’s total unused server capacity.
Let's start by joining both tables to obtain the data centre ID, monthly allocated capacity and monthly server demand.
Note that the represents the total server capacity for each centre whereas the is the server demand for each customer.
Thus, to get the total server demand by data centres, we have to add up the using the function and group by the data centres and monthly capacity.
To find the total unused capacity for each data centre, we can simply deduct the total spare capacity against the total capacity and then order the data centre IDs in ascending order.