Data Interview Qs - Garbage pickups and remaining capacity

Garbage pickups and remaining capacity

SQL Database Querying Aggregation Functions Join

Forward this email to a friend or colleague and challenge them to solve it.

Hi,

Suppose you're given two data tables, containing information about the capacity of various unique landfills, along with the associated pickups their trucks are responsible for. A description of the fields is below:

table: landfill_info

landfill_id - the unique ID of the landfill
current_weight_tons - the current weight (in tons) of trash at the landfill
capacity_tons - the max capacity (in tons) of trash at the landfill

table: landfill_routes

landfill_id - the unique ID of the landfill
num_pickups - the number of pickups the landfill needs to make (you can ignore the period of time in which the pickups need to be made for this question). Each pickup has an average weight of 0.02 tons

Additionally, below are the tables in their entirety:

landfill_info

landfill_id	current_weight_tons	capacity_tons
12300	95	200
12401	85	210
992	105	240
882	100	180
11100	55	100
11201	75	160
11207	60	110

landfill_routes

landfill_id	num_pickups
12300	1000
12401	500
992	750
882	800
11100	900
11201	400
11207	390

Using the tables above, write a SQL query to return the new current weight of each landfill (after accommodating all of the requested pickups), and return the amount of capacity remaining (capacity_tons - current_weight_tons). If the landfill is over capacity, you can express the remaining capacity as a negative number.

You can view/query the tables in an interactive SQL fiddle here.

You’ve accumulated 409 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.

Was this email forwarded to you? You can join our list here to receive questions