力扣数据库热题70--No.1164题解

官方难度中等，体感中等，需要稍微思考下。

这里先过滤出8月16号前的记录，再通过窗口函数找到离8月16号最近的日期打标，最后用全量表连接时再用ifnull做处理。

with tmp as
(select *,row_number() over(partition by product_id order by change_date desc) as rnk
from Products
where change_date <= '2019-08-16')

,tmp2 as
(select product_id,new_price
from tmp
where rnk = 1)

,tmp3 as
(select distinct product_id
from Products)

select tmp3.product_id as product_id,
    ifnull(new_price,10) as price
from tmp3 left join tmp2
on tmp3.product_id = tmp2.product_id

官方题解采用的是子查询，搭配max取最大日期。