LeetCode 코딩 테스트 - Product Price at a Given Date(LV.Medium)

 

 

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

 

2019-08-16일 기준으로 모든 제품의 가격을 찾는 SQL 쿼리를 작성하세요.
단, 가격 변경 이력이 없는 제품의 기본 가격은 10이라고 가정합니다.

 

WITH change_under AS (
    SELECT
        product_id,
        # 2. product_id 별 가장 최근의 날짜만 추출
        MAX(change_date) AS change_date
    FROM products
    # 1. ~20190816 의 기간만 사용
    WHERE
        change_date <= '2019-08-16'
    GROUP BY
        product_id
)
SELECT
    product_id,
    10 AS price
FROM products
GROUP BY
    product_id
# 3. product_id 별 가장 작은 날짜가 08-16 이후인 경우만 사용(= 가격 변경이 08-16 이후)
HAVING
    MIN(change_date) > '2019-08-16'
# 5. 테이블 병합
UNION
SELECT
    product_id,
    new_price AS price
FROM products
# 4. (~20190816)product_id별 max 날짜(가장 최근)에 일치하는 경우에 한해서 new_price 값 가져옴
WHERE
    (product_id, change_date) IN (SELECT product_id, change_date FROM change_under)