InterviewQs - Highest grossing items by location, without primary keys

DDL.SQL

CREATE TABLE item_prices_by_location (
  `city` VARCHAR(255),
  `item_name` VARCHAR(255),
  `price` INTEGER
);

INSERT INTO item_prices_by_location
  (`city`, `item_name`, `price`)
VALUES
  ('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')

;


CREATE TABLE item_sales_by_location (
  `city` VARCHAR(255),
  `item_name` VARCHAR(255),
  `num_sales` INTEGER
);

INSERT INTO item_sales_by_location
  (`city`, `item_name`, `num_sales`)
VALUES
  ('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')

;

InterviewQs - 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

Result.txt

city	item_name	price	num_sales	total_revenue	item_rank_by_city
Austin	Shirt	55	1600	88000	1
Austin	Pants	80	1000	80000	2
Austin	Coat	105	300	31500	3
Austin	Hat	30	200	6000	4
London	Coat	115	1400	161000	1
London	Pants	90	1300	117000	2
London	Shirt	65	1200	78000	3
London	Hat	40	700	28000	4
Los_Angeles	Shirt	60	1200	72000	1
Los_Angeles	Pants	85	800	68000	2
Los_Angeles	Coat	110	500	55000	3
Los_Angeles	Hat	35	450	15750	4
New_York	Coat	115	1055	121325	1
New_York	Shirt	65	1400	91000	2
New_York	Pants	90	1000	90000	3
New_York	Hat	40	900	36000	4

Solution.sql

SELECT
	 item_prices_by_location.city
	,item_prices_by_location.item_name
	,item_prices_by_location.price
	,item_sales_by_location.num_sales
	,item_prices_by_location.price * item_sales_by_location.num_sales as total_revenue
	,RANK() OVER (PARTITION BY item_prices_by_location.city ORDER BY item_prices_by_location.price * item_sales_by_location.num_sales DESC) AS item_rank_by_city
FROM
	item_prices_by_location
JOIN
	item_sales_by_location ON item_prices_by_location.city = item_sales_by_location.city AND item_prices_by_location.item_name = item_sales_by_location.item_name