[MYSQL] Product Price at a Given Date

Problem

[LeetCode] 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.

문제

Q. 2019-08-16의 모든 상품의 가격을 찾아내기. 가격이 변동하기 전에는 10으로 가정하시오

예제

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

---

Solution

1. 기준 날짜(8/16) 이전 가격 가져오기

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

• product_id : 1 -> 8/16 가격 35
  product_id : 2 -> 8/14 가격 50
  product_id : 3 -> 8/16 이전 가격 정보 안나와 있음

2. 기준 날짜(8/16) 이전 가격 변동 이력 없는 경우

기준 날짜 이전에 가격변동이 없었던 product_id 는 가격을 10으로 설정한다.

select distinct product_id, 10 as price
from products 
where product_id not in(
    select distinct product_id from products
    where change_date <= '2019-08-16'
)

** Union 으로 묶기 위해선 두 테이블의 select 컬럼의 개수가 동일해야함!

3. 두 결과 Union으로 묶기

최종 쿼리

select distinct product_id, 10 as price
from products 
where product_id not in(
    select distinct product_id from products
    where change_date <= '2019-08-16'
)
union
select product_id, new_price as price 
from products
where (product_id, change_date) in (
    select product_id, max(change_date) as date
    from products
    where change_date <= '2019-08-16'
    group by product_id
)