SQL | LeetCode 1164. 모든 제품의 가격 구하기

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

• (product_id, change_date)는 기본키입니다.
• 이 테이블은 제품의 가격이 특정 날짜에 얼마로 변경되었는지를 기록합니다.
• 변경이 일어나기 전의 가격은 모두 10입니다.

(SELECT DISTINCT product_id FROM Products) p

Products 테이블에서 중복 없이 product_id 목록만 뽑아옵니다.

(
    SELECT new_price
    FROM Products p2
    WHERE p2.product_id = p.product_id
      AND p2.change_date <= '2019-08-16'
    ORDER BY p2.change_date DESC
    LIMIT 1
)

이건 각 product마다 2019-08-16 이하의 가장 최근 가격을 구하는 부분입니다.

| change_date | new_price |
|-------------|-----------|
| 2019-08-14  | 20        |
| 2019-08-15  | 30        |
| 2019-08-16  | 5         | ← 가장 최근

예를 들어, product_id = 1이라면 결과는 5가 나옵니다.

COALESCE((
        SELECT new_price
        FROM Products p2
        WHERE p2.product_id = p.product_id
          AND p2.change_date <= '2019-08-16'
        ORDER BY p2.change_date DESC
        LIMIT 1
    ), 10) AS price

만약 해당 날짜 이하로 가격 변경 이력이 없으면, 즉 서브쿼리 결과가 NULL이면 기본값 10을 반환합니다.

SELECT 
    product_id,
    COALESCE((
        SELECT new_price
        FROM Products p2
        WHERE p2.product_id = p.product_id
          AND p2.change_date <= '2019-08-16'
        ORDER BY p2.change_date DESC
        LIMIT 1
    ), 10) AS price
FROM 
    (SELECT DISTINCT product_id FROM Products) p;