# $1384. 按年度列出销售总额
# https://leetcode-cn.com/problems/total-sales-amount-by-year/

# SQL架构
Create table If Not Exists Product (product_id int, product_name varchar(30));
Create table If Not Exists Sales (product_id varchar(30), period_start date, period_end date, average_daily_sales int);
Truncate table Product;
insert into Product (product_id, product_name) values ('1', 'LC Phone ');
insert into Product (product_id, product_name) values ('2', 'LC T-Shirt');
insert into Product (product_id, product_name) values ('3', 'LC Keychain');
Truncate table Sales;
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100');
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10');
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1');

# Write your MySQL query statement below
select
    s.product_id,
    p.product_name,
    y.year report_year,
    s.average_daily_sales * (
        if(
            year(s.period_end) > y.year,
            y.days_of_year,
            dayofyear(s.period_end)
        ) - if(
            year(s.period_start) < y.year,
            1,
            dayofyear(s.period_start)
        ) + 1
    ) as total_amount
from
    Sales s
    inner join (
        select
            '2018' as year,
            365 as days_of_year
        union
        all
        select
            '2019' as year,
            365 as days_of_year
        union
        all
        select
            '2020' as year,
            366 as days_of_year
    ) y on year(s.period_start) <= y.year
    and year(s.period_end) >= y.year
    inner join Product p on p.product_id = s.product_id
order by
    s.product_id,
    y.year;

# clean-up
drop table Product;
drop table Sales;