Projecting landfill volume
Question
Suppose you are given two tables containing information around various landfills across the United States. The table columns are defined as follows:
table: landfill_weights
- landfillID - the unique ID of the landfill
- base_weight - the current weight (in tons) of trash at the landfill
- number_garbage_vehicles - the number of garbage vehicles available in the fleet
table: landfill_monthly_flows
- monthly_inflow - the monthly inflow of trash to the landfill (in tons)
- monthly_outflow - the monthly outflow of trash to the landfill (in tons) through incineration
Using the tables below, write a SQL query to return the projected base_weight of landfills by state in 3 months. You can view/query the data in an interactive fiddle here.
landfill_weights
| landfill_id | base_weight | state | number_garbage_vehicles |
|---|---|---|---|
| 12300 | 95 | California | 1005 |
| 12401 | 85 | California | 850 |
| 00992 | 105 | New York | 1300 |
| 00882 | 100 | New York | 1000 |
| 11100 | 55 | Michigan | 580 |
| 11201 | 75 | Michigan | 700 |
| 11207 | 60 | Michigan | 500 |
landfill_monthly_flows
| monthly_inflow | monthly_outflow | landfill_id |
|---|---|---|
| 12300 | 23 | 17 |
| 12401 | 17 | 15 |
| 00992 | 25 | 20 |
| 00882 | 22 | 18 |
| 11100 | 6 | 4 |
| 11201 | 12 | 9 |
| 11207 | 10 | 8 |