[SQL] Product Price at a Given Date

문제 링크

https://leetcode.com/problems/product-price-at-a-given-date/description/

문제 설명

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

접근 방식

내 풀이

with a as (
SELECT product_id, MAX(change_date) change_date
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY product_id
)
SELECT p.product_id, p.new_price price
FROM Products p JOIN a
ON p.product_id = a.product_id AND p.change_date = a.change_date
UNION
SELECT product_id, 10 AS price
FROM Products
GROUP BY 1
HAVING MIN(change_date) > '2019-08-16'

다른 풀이

(Editorial) - Divide cases by using UNION ALL

SELECT
  UniqueProductId.product_id,
  IFNULL (LastChangedPrice.new_price, 10) AS price
FROM
  (
    SELECT DISTINCT
      product_id
    FROM
      Products
  ) AS UniqueProductId
  LEFT JOIN (
    SELECT
      Products.product_id,
      new_price
    FROM
      Products
      JOIN (
        SELECT
          product_id,
          MAX(change_date) AS change_date
        FROM
          Products
        WHERE
          change_date <= "2019-08-16"
        GROUP BY
          product_id
      ) AS LastChangedDate USING (product_id, change_date)
    GROUP BY
      product_id
  ) AS LastChangedPrice USING (product_id)

Use the window function

SELECT
  product_id,
  IFNULL (price, 10) AS price
FROM
  (
    SELECT DISTINCT
      product_id
    FROM
      Products
  ) AS UniqueProducts
  LEFT JOIN (
    SELECT DISTINCT
      product_id,
      FIRST_VALUE (new_price) OVER (
        PARTITION BY
          product_id
        ORDER BY
          change_date DESC
      ) AS price
    FROM
      Products
    WHERE
      change_date <= '2019-08-16'
  ) AS LastChangedPrice USING (product_id);

COALESCE 기반 해결 방법

SELECT 
    product_id,
    COALESCE(
        (SELECT new_price 
         FROM Products p2
         WHERE p2.product_id = p1.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) p1;