LeetCode 1082,1083,1084 Sales Analysis

Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int)
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int)
Truncate table Product
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000')
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800')
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400')
Truncate table Sales
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000')
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800')
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800')
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800')

 

1082. Sales Analysis I

Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.

CTE:

with cte as (
select seller_id,sum(price) as total_price
from Sales
group by seller_id
),cte2 as (
select seller_id,rank() over(order by total_price desc) as rnk
from cte
) select seller_id 
from cte2 
where rnk=1

Having

select seller_id
from Sales
group by seller_id
having sum(price)=
(
select top 1 sum(price) from sales group by seller_id order by 1 desc
)

 

1083. Sales Analysis II

Write an SQL query that reports the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products present in the Product table.

 

select distinct buyer_id
from Sales s join Product p on
s.product_id=p.product_id
where p.product_name='S8'
and buyer_id not in
(
select distinct buyer_id
from Sales s join Product p on
s.product_id=p.product_id
where p.product_name='iPhone'
)

 

Using sum:

SELECT s.buyer_id
FROM Sales AS s INNER JOIN Product AS p
ON s.product_id = p.product_id
GROUP BY s.buyer_id
HAVING SUM(CASE WHEN p.product_name = 'S8' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.product_name = 'iPhone' THEN 1 ELSE 0 END) = 0

 

1084. Sales Analysis III

Write an SQL query that reports the products that were only sold in spring 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

 

Having

select s.product_id,p.product_name
from Sales s join Product p on 
s.product_id=p.product_id
group by s.product_id,p.product_name
having max(s.sale_date)<='2019-03-31'
and min(s.sale_date)>='2019-01-01'