Question

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

Solutions

Solution 1: Subquery + Join

We can use a subquery to find the price of the last price change for each product before the given date, and record it in the P table. Then, we can find all product_ids in the T table. Finally, we can left join the T table with the P table on product_id to get the final result.

# Write your MySQL query statement below
WITH
  T AS (SELECT DISTINCT product_id FROM Products),
  P AS (
    SELECT product_id, new_price AS price
    FROM Products
    WHERE
      (product_id, change_date) IN (
        SELECT product_id, MAX(change_date) AS change_date
        FROM Products
        WHERE change_date <= '2019-08-16'
        GROUP BY 1
      )
  )
SELECT product_id, IFNULL(price, 10) AS price
FROM
  T
  LEFT JOIN P USING (product_id);

Solution 2

# Write your MySQL query statement below
WITH
  P AS (
    SELECT p1.product_id, new_price, change_date
    FROM
      (
        SELECT DISTINCT product_id
        FROM Products
      ) AS p1
      LEFT JOIN Products AS p2
        ON p1.product_id = p2.product_id AND p2.change_date <= '2019-08-16'
  ),
  T AS (
    SELECT
      *,
      RANK() OVER (
        PARTITION BY product_id
        ORDER BY change_date DESC
      ) AS rk
    FROM P
  )
SELECT product_id, IFNULL(new_price, 10) AS price
FROM T
WHERE rk = 1;