LeetCode 1070. Product Sales Analysis III SQL Solution

Problem

LeetCode SQL Problem

1070. Product Sales Analysis III

Sales table

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product table

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Solution

MySQL

Product Sales Analysis III

-- Find the first sale year of every product
WITH ProductFirstYear
AS (
	SELECT product_id
		,min(year) AS first_year
	FROM Sales
	GROUP BY product_id
	)
-- Select the product id, year, quantity, and price for the first year of every product sold.
SELECT S.product_id
	,PFY.first_year
	,S.quantity
	,S.price
FROM Sales AS S
INNER JOIN ProductFirstYear AS PFY ON S.product_id = PFY.product_id
	AND S.year = PFY.first_year

TSQL

Product Sales Analysis III

-- Find the first sale year of every product
WITH ProductFirstYear
AS (
	SELECT product_id
		,min(year) AS first_year
	FROM Sales
	GROUP BY product_id
	)
-- Select the product id, year, quantity, and price for the first year of every product sold.
SELECT S.product_id
	,PFY.first_year
	,S.quantity
	,S.price
FROM Sales AS S
INNER JOIN ProductFirstYear AS PFY ON S.product_id = PFY.product_id
	AND S.year = PFY.first_year

Query Output

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000