State landfills


Suppose you are given the following table, containing information around total tonnage of trash for various landfills across various states. In other words, each row represents the total weight (in tons) of trash at a specific landfill site in a specific state.


landfillID 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

Using the above table, write a SQL query to return the landfill with the second highest amount of garbage (based on weight) for each state shown. You can assume each row represents a unique landfill (e.g. the weights shown are the total weights, and do not need further aggregation) and each weight happens to be unique (e.g. there are no ties).


Access restricted

Subscribe to premium account to see the solution.

Get premium now