Garbage pickups and remaining capacity
Question
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.