30 Days Of LeetCode Database Day 29 - 1164. Product Price at a Given Date

1Table: Products
2 
3+---------------+---------+
4| Column Name   | Type    |
5+---------------+---------+
6| product_id    | int     |
7| new_price     | int     |
8| change_date   | date    |
9+---------------+---------+
10(product_id, change_date) is the primary key (combination of columns with unique values) of this table.
11Each row of this table indicates that the price of some product was changed to a new price at some date.
12Initially, all products have price 10.
13 
14Write a solution to find the prices of all products on the date 2019-08-16.
15 
16Return the result table in any order.
17 
18The result format is in the following example.

1Example 1:
2 
3Input: 
4Products table:
5+------------+-----------+-------------+
6| product_id | new_price | change_date |
7+------------+-----------+-------------+
8| 1          | 20        | 2019-08-14  |
9| 2          | 50        | 2019-08-14  |
10| 1          | 30        | 2019-08-15  |
11| 1          | 35        | 2019-08-16  |
12| 2          | 65        | 2019-08-17  |
13| 3          | 20        | 2019-08-18  |
14+------------+-----------+-------------+
15Output: 
16+------------+-------+
17| product_id | price |
18+------------+-------+
19| 2          | 50    |
20| 1          | 35    |
21| 3          | 10    |
22+------------+-------+
23 

1**SQL**
2 
3SELECT p.product_id, 
4IFNULL(
5    (SELECT new_price FROM Products 
6        WHERE product_id = p.product_id
7        AND change_date <= "2019-08-16"
8        ORDER BY change_date DESC LIMIT 1
9    ),
10     10
11) AS  price
12FROM Products p GROUP BY p.product_id;