1082. Sales Analysis I

Question:

https://leetcode.com/problems/sales-analysis-i/

Solution:

select 
seller_id
from (
    select 
    seller_id, 
    rank() over (order by price desc) r
    from (
        select 
        s.seller_id, 
        sum(s.price) as price
        from Sales s
        group by 1
    ) a
) b
where r = 1
order by 1

1083. Sales Analysis II

Question:

https://leetcode.com/problems/sales-analysis-ii/

Solution:

select distinct 
buyer_id
from (
    select 
    buyer_id,
    case 
        when buyer_id in (
            select distinct 
            buyer_id
            from Sales s
                join Product p 
                    on s.product_id = p.product_id
            where product_name = 'iPhone'
        ) then 1 
        else 0 
    end as iphone_buyer,
    case 
        when buyer_id in (
            select distinct 
            buyer_id
            from Sales s
                join Product p 
                    on s.product_id = p.product_id
            where product_name = 'S8'
        ) then 1 
        else 0 
    end as s8_buyer
    from Sales
) a
where iphone_buyer = 0 
and s8_buyer = 1

1084. Sales Analysis III

Question:

https://leetcode.com/problems/sales-analysis-iii/

Solution:

select distinct 
a.product_id, 
a.product_name
from(
    select distinct 
    s.product_id, 
    p.product_name, 
    min(s.sale_date) as min_sale_date, 
    max(s.sale_date) as max_sale_date
    from Sales s
        left join Product p 
            on s.product_id = p.product_id
    where quantity >=1
    group by 1,2
) a
where (
    a.min_sale_date >= '2019-01-01' 
    and a.min_sale_date <= '2019-03-31'
)
and (
    a.max_sale_date >= '2019-01-01' 
    and a.max_sale_date <= '2019-03-31'
)

Leave a Reply