리트코드 : 1164. Product Price at a Given Date

리트코드 : 1164. Product Price at a Given Date

• [link]
  

---

문제

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

• 8월 16일의 PRICE 구하기

문제 풀이

MySQL

WITH B16 AS (
    SELECT *
    FROM (
        SELECT PRODUCT_ID, NEW_PRICE AS PRICE_A,
            ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY CHANGE_DATE DESC) AS IDX
        FROM PRODUCTS
        WHERE CHANGE_DATE <= '2019-08-16') AS B
    WHERE IDX = 1
),
A16 AS (
    SELECT *
    FROM (
        SELECT PRODUCT_ID, NEW_PRICE AS PRICE_B,
            ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY CHANGE_DATE ASC) AS IDX
        FROM PRODUCTS
        WHERE CHANGE_DATE > '2019-08-16') AS A
    WHERE IDX = 1
),
TEMP AS (
    SELECT B.PRODUCT_ID, B.PRICE_A, A.PRICE_B
    FROM B16 B
    LEFT JOIN A16 A ON B.PRODUCT_ID = A.PRODUCT_ID

    UNION

    SELECT A.PRODUCT_ID, B.PRICE_A, A.PRICE_B
    FROM B16 B
    RIGHT JOIN A16 A ON B.PRODUCT_ID = A.PRODUCT_ID
    )

SELECT PRODUCT_ID,
        CASE
            WHEN PRICE_A IS NULL THEN 10
            ELSE PRICE_A
        END AS PRICE
FROM TEMP

• 호흡이 길어서 조금 난도가 있었다.
• 첫 번째로는 8.16일을 기점으로 테이블을 수평분할하기.
• 각 분할 테이블에서 날짜를 기준으로 ROW_NUMBER를 부여하고, IDX = 1인 정보들을 가져온다.
• BEFORE 16 테이블에서는 DESC로 해서 날짜가 가장 큰 것, AFTER 16 테이블에서는 ASC로 해서 날짜가 가장 작은 것
• 이후 두 테이블을 OUTER JOIN해주기. MYSQL도 OUTER JOIN 지원 해라...
• 8.16 이전 변경 데이터가 없으면 10, 그게 아니면 변경 이후 데이터를 가져오면 된다.

Pandas

import pandas as pd

def price_at_given_date(products: pd.DataFrame) -> pd.DataFrame:

    cutoff_date = pd.to_datetime('2019-08-16')

    b16 = (products[products['change_date'] <= cutoff_date]
           .sort_values(by=['product_id', 'change_date'], ascending=[True, False])
           .drop_duplicates('product_id', keep='first')
           .rename(columns={'new_price': 'price_a'}))

    a16 = (products[products['change_date'] > cutoff_date]
           .sort_values(by=['product_id', 'change_date'], ascending=[True, True])
           .drop_duplicates('product_id', keep='first')
           .rename(columns={'new_price': 'price_b'}))

    temp = pd.merge(b16, a16, on='product_id', how='outer')
    temp['price'] = temp.apply(lambda row: 10 if pd.isna(row['price_a']) else (row['price_a'] if pd.isna(row['price_b']) else row['price_a']), axis=1)

    return temp[['product_id', 'price']]

• 같은 로직으로

코멘트

• 요 근래 풀었던 리트코드 쿼리중에 제일 어려운듯