1164. Product Price at a Given Date.md

1164. Product Price at a Given Date

Question Link

Solution v1

select product_id, 10 price from Products where product_id not in (
  select product_id from Products where change_date<='2019-08-16'
)
union
select product_id, new_price price from Products where (product_id, change_date) in (
  select product_id, max(change_date) date from Products where change_date<='2019-08-16' group by product_id
)

Solution v2

select p1.product_id, ifnull(p2.new_price, 10) as price from (
    select distinct product_id from products
) as p1
left join
(
    select product_id, new_price from products where (product_id, change_date) in (
select product_id, max(change_date) as max_date from products
where change_date <= '2019-08-16' group by product_id
)
) as p2 on p1.product_id = p2.product_id