Blog

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.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course