This Leetcode problem Product Price at a Given Date LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Product Price at a Given Date LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Product Price at a Given Date LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
product_id | int |
new_price | int |
change_date | date |
Products
(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:
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 |
Product Price at a Given Date LeetCode Solution MySQL
select
i.product_id,
max(
if(
i.product_id not in (
select
product_id
from
Products
where
change_date <= date '2019-08-16'
group by
product_id
),
10,
(
select
new_price
from
Products
where
product_id = i.product_id
and product_id = q.product_id
and change_date = q.max_change_date
)
)
) as price
from
(
select
distinct product_id
from
Products
) as i,
(
select
product_id,
max(change_date) as max_change_date
from
Products
where
change_date <= date '2019-08-16'
group by
product_id
) as q
group by
i.product_id;
Code language: SQL (Structured Query Language) (sql)