## 1384. Total Sales Amount by Year

Question:

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:

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 (
and s.next_operation = 'Sell'
)
group by 1``````

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

Question:

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:

``````select