- In this article we will solve Spare Server Capacity SQL question which is asked by Microsoft as per DataLemur website.
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
- We have been given customer, datacenter and forecasted_demand tables.
- We need to find out monthly unused server capacity for each datacenter.
- At first , we will join forecasted_demand and datacenters table and we will select monthly demand and monthly capacity for each datacenter.
- Reason of joining this table is because we want to get monthly demand and monthly capacity for each customer. Our joining key would datacenter_id.
- Once we have monthly capacity and monthly demand for each datacenter then it becomes easier for us to calculate spare capacity by just subtracting the two as shown below.
- We used joining sql query as subquery. please find more about sql query here if you don’t know.
- Here is the final solution for the problem.
select datacenter_id, (monthly_capacity - sum(monthly_demand)) as spare_capacity from( select f.datacenter_id, f.monthly_demand, d.monthly_capacity from forecasted_demand f join datacenters d on f.datacenter_id = d.datacenter_id ) x group by datacenter_id, monthly_capacity order by datacenter_id
- Our solution get accepted by the platform.
- In this article, we have solved sql question asked by Microsoft. In this article we used subquery and SQL inner join to solve the problem.