logo

Back to questions

Spare Server Capacity

Easy

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?

Table:

Column NameType
namestring
customer_idinteger

Example Input:

namecustomer_id
Florian Simran144
Esperanza A. Luna109
Garland Acacia852

Table:

Column NameType
datacenter_idinteger
namestring
monthly_capacityinteger

Example Input:

datacenter_idnamemonthly_capacity
1London100
3Amsterdam250
4Hong Kong400

Table:

Column NameType
customer_idinteger
datacenter_idinteger
monthly_demandinteger

Example Input:

customer_iddatacenter_idmonthly_demand
1094120
144360
1444105
852160
8523178

Example Output:

datacenter_idspare_capacity
140
312
4175

PostgreSQL 14

Refer friends to get bonus content & cool prizes.