[leetcode]1164. Product Price at a Given Date

문제

https://leetcode.com/problems/product-price-at-a-given-date/?envType=study-plan-v2&envId=top-sql-50

포인트!

• change_date가 2019-08-16 이전일때 MAX 값.
• 2019-08-16 이전일때 price가 없다면 10으로 간주

코드

WITH
    DT AS(
        SELECT
            DISTINCT product_id
        FROM
            Products
    ),
    P AS(
        SELECT
            product_id, new_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)
    )
SELECT
    DT.product_id,
    IFNULL(P.new_price,10) AS price
FROM
    DT
    LEFT JOIN P
        ON DT.product_id=P.product_id