Highest grossing items by location, without primary keys


Suppose you’re given the following tables, showing item prices by location as well as item sales by location:

Table: item_prices_by_location

city item_name price
New_York Coat 115
New_York Hat 40
New_York Shirt 65
New_York Pants 90
Los_Angeles Coat 110
Los_Angeles Hat 35
Los_Angeles Shirt 60
Los_Angeles Pants 85
Austin Coat 105
Austin Hat 30
Austin Shirt 55
Austin Pants 80
London Coat 115
London Hat 40
London Shirt 65
London Pants 90

Table: item_sales_by_location

city item_name num_sales
New_York Coat 1055
New_York Hat 900
New_York Shirt 1400
New_York Pants 1000
Los_Angeles Coat 500
Los_Angeles Hat 450
Los_Angeles Shirt 1200
Los_Angeles Pants 800
Austin Coat 300
Austin Hat 200
Austin Shirt 1600
Austin Pants 1000
London Coat 1400
London Hat 700
London Shirt 1200
London Pants 1300

Using these tables, write a SQL query to return total revenue by location by item (num_sales*price), and add a column that ranks each item within a given city based on total revenue in descending order. In other words, we want to see which items in each city drive the most revenue.

Note/hint: you’ll notice these tables do not offer a set of primary keys at the location/city level, so you’ll have to come up with a way to generate your own primary key to join them together

Click here to view/query the tables in an interactive SQL editor.


Access restricted

Subscribe to premium account to see the solution.

Get premium now