union, union all

SELECT product_id, 
    FIRST_VALUE(new_price) OVER(PARTITION BY product_id ORDER BY change_date DESC) AS price
FROM Products
WHERE change_date <= '2019-08-16'

UNION

SELECT DISTINCT product_id, 10 AS price 
FROM Products
WHERE product_id NOT IN (SELECT product_id 
                        FROM Products
                        WHERE change_date <= '2019-08-16')

- 8월 16일 까지는 최근 가격

- 8월 16일 안으로 갱신된 가격이 없으면 10달러

- 중복 제거를 위해 union

 

 

SELECT product_id, new_price AS 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)

union all

select product_id,10 as price
from Products
group by product_id
having min(change_date)>'2019-08-16'

- 8월 16일 이전까지는 최근 갱신 날짜 가격

- 최초 갱신 날짜가 8월 16일 이후면 10달러

- 중복 안생기므로 union all 가능