1164. Product Price at a Given Date

Level: Medium

Question:

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.

Return the result table in any order.

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

My Solution:

Split the task into two phases: find the price that have change_date most closer to the left hand side of '2019-08-16'; union with products that does not have price before '2019-08-16' and set their price to 10.

select product_id, new_price as price
from Products
where (product_id,change_date) in
(select product_id, max(change_date) as date
from Products
where change_date<='2019-08-16'
group by product_id)

union 

select product_id, 10 as price
from Products
group by product_id
having min(change_date)>'2019-08-16'