Spare Server Capacity
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 it's data centers. You’re given three tables: customers, datacenters, and forecasted_demand.
Write a query to find the total monthly unused server capacity for each data center. Output the data center id in ascending order and the total spare capacity.
P.S. If you've read the Ace the Data Science Interview and liked it, consider writing us a review?
|Esperanza A. Luna||109|
First, we need to find the total demand for each data center. This can be done by querying the table and summing up the while simultaneously grouping on .
Output shows the total demand grouped by the data centers.
As the next step is to calculate the spare capacity, we will need to join the query above with the table to retrieve the total monthly_capacity. Hence, using the results from previous step as a subquery or CTE, we join it with the table using the related datacenter_id field.
So, now that we have the total demand and total capacity by data center, we can simply just deduct the total demand against total capacity to obtain the spare capacity.
A CTE is a temporary data set to be used as part of a query and it exists during the entire query session. A subquery is a nested query. It’s a query within a query and unlike CTE, it can be used within that query only. Read here and here for more details.
Both methods give the same output and perform fairly similarly. Differences are CTE is reusable during the entire session and more readable, whereas subquery can be used in FROM and WHERE clauses and can act as a column with a single value. We share more resources here (1, 2, 3 on their use cases.
Solution #2: Using subquery