Garbage pickups and remaining capacity


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


Access restricted

Subscribe to premium account to see the solution.

Get premium now