Spare Server Capacity — Microsoft Asked SQL Question

  • Post last modified:September 11, 2022
  • Reading time:2 mins read

Photo by Caspar Camille Rubin on Unsplash

Introduction

  • 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.

Question

  • We have been given customer, datacenter and forecasted_demand tables.
  • We need to find out monthly unused server capacity for each datacenter.

Solution

  • 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.

Final Solution

  • 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

Submission

  • Our solution get accepted by the platform.

Conclusion

  • 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.

Leave a Reply