1384. Total Sales Amount by Year

Question:

https://leetcode.com/problems/total-sales-amount-by-year/

Solution:

select 
product_id, 
product_name, 
year as report_year, 
(days_count * average_daily_sales) as total_amount
from(
    select d.*, s.*,
    case 
    /* start and end in the same year */
    when year(s.period_end)-year(s.period_start) = 0 then datediff(period_end, period_start)+1 
    /* start and end across multiple years */
    when year(s.period_end)-year(s.period_start) > 0 then 
        case 
            when year(s.period_start) = d.year then datediff(d.year_end, s.period_start) +1
            when year(s.period_end) = d.year then datediff(s.period_end, d.year_start) +1
            when year(s.period_start) < d.year and year(s.period_end) > d.year then datediff(d.year_end, d.year_start) +1
        end
    end as days_count
    from(
        select 
        date_format('2018-12-31','%Y') as year, 
        date_format('2018-01-01','%Y-%m-%d') as year_start, 
        date_format('2018-12-31','%Y-%m-%d') as year_end
        union
        select 
        date_format('2019-12-31','%Y') as year, 
        date_format('2019-01-01','%Y-%m-%d') as year_start, 
        date_format('2019-12-31','%Y-%m-%d') as year_end
        union
        select 
        date_format('2020-12-31','%Y') as year, 
        date_format('2020-01-01','%Y-%m-%d') as year_start, 
        date_format('2020-12-31','%Y-%m-%d') as year_end
    ) d
    cross join (
        select 
        s.*, 
        p.product_name 
        from Sales s 
            left join Product p 
                on s.product_id = p.product_id
    ) s
    where (
    d.year >= year(s.period_start) 
    and d.year <= year(s.period_end)
        )
) a
order by product_id, year

1393. Capital Gain/Loss

Question:

https://leetcode.com/problems/capital-gainloss/

Solution:

select 
s.stock_name, 
sum(s.sell_price - s.price) as capital_gain_loss
from(
    select 
    *, 
    lead(operation,1) over(partition by stock_name order by stock_name, operation_day) as next_operation,
    lead(price,1) over(partition by stock_name order by stock_name, operation_day) as sell_price
    from Stocks
) s
where (
    s.operation= 'Buy' 
    and s.next_operation = 'Sell'
    )
group by 1

1398. Customers Who Bought Products A and B but Not C

Question:

https://leetcode.com/problems/customers-who-bought-products-a-and-b-but-not-c/

Solution:

select 
o.customer_id, 
c.customer_name
from orders o
    left join customers c 
        on o.customer_id = c.customer_id
where (
o.product_name in ('A','B')
and o.customer_id not in (
    select distinct 
    customer_id
    from orders
    where product_name = 'C') 
)
group by 1,2
having count(distinct product_name) >1
order by o.customer_id

1407. Top Travellers

Question:

https://leetcode.com/problems/top-travellers/

Solution:

select 
u.name, 
ifnull(sum(r.distance),0) as travelled_distance
from Users u
    left join Rides r 
        on u.id = r.user_id
group by 1
order by 2 desc, 1

Leave a Reply