LC 1384. Total Sales Amount by Year

# Write your MySQL query statement below

select t3.product_id,p.product_name,year as report_year,amount as total_amount from 
(
select product_id,year,if(period_start>end or period_end<start,-1,(1+datediff(if(period_end>end,end,period_end),if(period_start<start,start,period_start)))*average_daily_sales) as amount
from
(
select * from Sales join 
(
select ‘2018‘ as year, ‘2018-01-01‘ as start, ‘2018-12-31‘ as end
union all
select ‘2019‘ as year, ‘2019-01-01‘ as start, ‘2019-12-31‘ as end
union all
select ‘2020‘ as year, ‘2020-01-01‘ as start, ‘2020-12-31‘ as end
)t1
)t2 
)t3 join Product p where t3.product_id=p.product_id and t3.amount>-1 
order by t3.product_id, report_year