[SQL #110]1164. Product Price at a Given Date

1164. Product Price at a Given Date

문제 개요

나의 방법: CTE + CASE WHEN

1. 2019-08-16 기준으로 product grouping 하여 하나의 CTE filtered를 만들고 모든 product_id를 가져오기 위해 product_id 기준 grouping만 수행한 CTE original을 만들었다.

2. 두 개의 CTE를 LEFT JOIN하여 original의 모든 product_id를 살리고 발생한 null 값을 10으로 바꿔줬다.

• 문제는 여기서 new_price가 원래 price보다 더 작아지는 경우에 발생했다.
  나의 max함수는 무용지물이 되어버렸다..

with filtered as
    (select product_id, max(new_price) price
    from products
    where change_date <= '2019-08-16'
    group by product_id)
, original as
    (select product_id
    from products
    group by product_id)

select 
    o.product_id product_id,
    case when price is null then 10 else price end 'price'
from original o left join filtered f on o.product_id = f.product_id

방법1: UNION ALL

SELECT  
  product_id,
  10 AS price
FROM
  Products
GROUP BY
  product_id
HAVING
  MIN(change_date) > '2019-08-16'
UNION ALL
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
  )