select product_id, ifnull(max(b.new_price),10) pricefrom Products a left join(select*, rank() over (partitionby product_id order by change_date desc) rkfrom Productswhere change_date <='2019-08-16') b using(product_id) where rk=1or rk isnullgroup by1