[SQL 문제 풀이] Product Price at a Given Date (지정된 날짜의 제품 가격)

Product Price at a Given Date - LeetCode

Can you solve this real interview question? Product Price at a Given Date - Table: Products +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ (product_id, change_date) is the primary key (combination of columns with unique values) of this table. Each row of this table indicates that the price of some product was changed to a new price at some date.   Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10. Return the result table in any order. The result format is in the following example.   Example 1: Input: Products table: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ Output: +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+

https://leetcode.com/problems/product-price-at-a-given-date/description/

처음에는 모든 제품의 가격이 10입니다.

2019-08-16일에 모든 제품의 가격을 찾기 위한 해결책을 작성하세요.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL

-- 8월 16일 이전것들 추출하는 CTE
WITH price_history AS (
    SELECT *
    FROM Products
    WHERE change_date <= '2019-08-16'
),
-- 각 id별 가장 마지막으로 변경된 날 추출하는 CTE
latest_price AS (
    SELECT
        product_id,
        MAX(change_date) AS latest_date
    FROM price_history
    GROUP BY product_id
)

SELECT
    p.product_id,
    IFNULL(ph.new_price, 10) AS price
FROM (
    SELECT DISTINCT product_id FROM Products
) p
LEFT JOIN latest_price lp -- id 기준으로 JOIN
    ON p.product_id = lp.product_id
LEFT JOIN price_history ph -- id, 날짜 기준으로 JOIN
    ON p.product_id = ph.product_id
        AND lp.latest_date = ph.change_date
ORDER BY p.product_id;

Oracle

-- 8월 16일 이전것들 추출하는 CTE
WITH price_history AS (
    SELECT *
    FROM Products
    WHERE change_date <= TO_DATE('2019-08-16', 'YYYY-MM-DD')
),
-- 각 id별 가장 마지막으로 변경된 날 추출하는 CTE
latest_price AS (
    SELECT
        product_id,
        MAX(change_date) AS latest_date
    FROM price_history
    GROUP BY product_id
)

SELECT
    p.product_id,
    NVL(ph.new_price, 10) AS price
FROM (
    SELECT DISTINCT product_id FROM Products
) p
LEFT JOIN latest_price lp -- id 기준으로 JOIN
    ON p.product_id = lp.product_id
LEFT JOIN price_history ph -- id, 날짜 기준으로 JOIN
    ON p.product_id = ph.product_id
    AND lp.latest_date = ph.change_date
ORDER BY p.product_id;

차이점