LeetCode 1068 1069 Product Sales Analysis

drop table Product
drop table Sales
Create table Sales(sale_id int,product_id int,year int,quantity int,price int);
Create table Product(product_id int,product_name varchar(20));

insert into Sales values(1,100,2008,10,5000);
insert into Sales values(2,100,2009,12,5000);
insert into Sales values(7,200,2011,15,9000);


insert into Product values(100,'Nokia');
insert into Product values(200,'Apple');
insert into Product values(300,'Samsung');

select * from Product
select * from Sales

 

1068. Product Sales Analysis I

 

Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price.

select p.product_name,s.year,s.price
from Sales s
join Product p on s.product_id=p.product_id

1069. Product Sales Analysis II

 

编写一个 SQL 查询，按产品 id product_id 来统计每个产品的销售总量

select product_id,sum(quantity) as total_quantity
from Sales
group by product_id

1070. Product Sales Analysis III

Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.

CTE Solution:

with cte as(
select product_id,min(year) as first_year
from Sales
group by product_id
)
select s.product_id,c.first_year,s.quantity,s.price
from Sales s join cte c on s.product_id = c.product_id
and s.year=c.first_year

Windows Function

select product_id,year as first_year,quantity,price
from 
(
select *,rank() over(partition by product_id order by year) as year_rnk
from Sales
) as tbl
where year_rnk=1