1164. 指定日期的产品价格（中等题）

select product_id,if(new_price>0,new_price,10) as ' price'
FROM
(select distinct product_id,10 as 'price'
from Products) u

left join 
    (SELECT product_id as 'nid',new_price
    FROM
    (select  product_id, new_price,change_date,
    #对月份逆向排序 选择最大的
    row_number() over(partition by  product_id order by change_date desc) as 'rank'
    from  Products
    #在 8月16日之内
    where change_date<='2019-08-16' )a
where a.rank =1 ) b on u.product_id = b.nid
order by price desc