# $1164. 指定日期的产品价格
# https://leetcode-cn.com/problems/product-price-at-a-given-date/

# SQL架构
Create table If Not Exists Products (product_id int, new_price int, change_date date);
Truncate table Products;
insert into Products (product_id, new_price, change_date) values ('1', '20', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('2', '50', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('1', '30', '2019-08-15');
insert into Products (product_id, new_price, change_date) values ('1', '35', '2019-08-16');
insert into Products (product_id, new_price, change_date) values ('2', '65', '2019-08-17');
insert into Products (product_id, new_price, change_date) values ('3', '20', '2019-08-18');

# Write your MySQL query statement below
select
    tmp1.product_id as product_id,
    ifnull(tmp2.new_price, 10) as price
from
    (
        select
            distinct product_id
        from
            Products
    ) tmp1
    left join (
        select
            product_id,
            new_price
        from
            Products
        where
            (product_id, change_date) in (
                select
                    product_id,
                    max(change_date)
                from
                    Products
                where
                    change_date <= '2019-08-16'
                group by
                    product_id
            )
    ) tmp2 on tmp1.product_id = tmp2.product_id;

# clean-up
drop table Products;