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.