Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1164.html
1164. Product Price at a Given Date
Level
Medium
Description
Table: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+
(product_id, change_date) is the primary key 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 an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
The query result format is in the following example:
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 |
+------------+-----------+-------------+
Result table:
+------------+-------+
| product_id | price |
+------------+-------+
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
+------------+-------+
Solution
For each product, consider two cases. The first case is that there is at least one entry of the product and the new price that has change date before or on 2019-08-16. The second case is that there is no entry of the product and the new price that has change date before on 2019-08-16.
For the first case, select product_id and new_price as price from table Products where change_date is the maximum value less than or equal to 2019-08-16.
For the second case, select product_id and 10 as price from table Products where product_id is not in the query that change_date <= '2019-08-16.
Finally, join the two sub-queries to obtain the final result.
# Write your MySQL query statement below
select * from (
select product_id, new_price as price from Products
where (product_id, change_date) in (
select product_id, max(change_date) from Products
where change_date <= '2019-08-16'
group by product_id
)
union
select distinct product_id, 10 as price from Products
where product_id not in (
select product_id from Products
where change_date <= '2019-08-16'
)
) union_result;