1164. Product Price at a Given Date

* Problem

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.

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    |
+------------+-------+

 

 

* Key Points✨

 

✅ 모든 product_id를 포함하여 기준 날짜 이전 최신 가격 조회

• Products에서 모든 product_id 가져오기 → (SELECT DISTINCT product_id FROM Products) p
• LEFT JOIN을 사용하여 기준 날짜 이전(<= '2019-08-16')의 최신 가격을 RankedProducts에서 조회.

✅ 날짜 기준으로 데이터가 없으면 new_price = 10으로 대체

• COALESCE(r.new_price, 10)을 사용하여 해당 날짜 이전 데이터가 없으면 10으로 대체

###### Step 01
SELECT product_id, 
       new_price, 
       change_date,
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
  FROM Products
  WHERE change_date <= '2019-08-16'
  
-- Output
| product_id | new_price | change_date | rn |
| ---------- | --------- | ----------- | -- |
| 1          | 35        | 2019-08-16  | 1  |
| 1          | 30        | 2019-08-15  | 2  |
| 1          | 20        | 2019-08-14  | 3  |
| 2          | 50        | 2019-08-14  | 1  |


###### Step 02
WITH RankedProducts AS (
    SELECT product_id, 
           new_price, 
           change_date,
           ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
    FROM Products
    WHERE change_date <= '2019-08-16'
)
SELECT p.product_id, 
       COALESCE(r.new_price, 10) AS price  -- 최신 가격이 없으면 10으로 대체
       r.change_date
    FROM (SELECT DISTINCT product_id FROM Products) p  -- 모든 product_id 가져오기
    LEFT JOIN RankedProducts r 
           ON p.product_id = r.product_id 
          AND r.rn = 1;
          
-- Output
| 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  |

 

 

* Solution(Success)

# Write your MySQL query statement below
WITH RankedProducts AS (
    SELECT product_id, 
           new_price, 
           change_date,
           ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
    FROM Products
    WHERE change_date <= '2019-08-16'
)
SELECT p.product_id, 
       COALESCE(r.new_price, 10) AS price
    FROM (SELECT DISTINCT product_id FROM Products) p
    LEFT JOIN RankedProducts r 
           ON p.product_id = r.product_id 
          AND r.rn = 1;

 

*** 동일한 sql query 여도 인터넷 상황 등.. 에 의해 runtime이 다르게 발생 할 수 있습니다.

동일한 쿼리 실행 결과