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.
Initially, all products have price 10.
Write a solution to find the prices of all products on the date 2019-08-16.
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.
sql
# 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);(code-box)
Solution 2
sql
# 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;(code-box)
