Product Price at a Given Date 11-11-22

link ------------------- https://www.codingninjas.com/codestudio/problems/product-price-at-a-given-date_2122057?topList=top-100-sql-problems&leftPanelTab=0


Problem Statement

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 1 -------------------------------


with newt as (select * , dense_rank() over(partition by product_id order by change_date desc) rank from products 
where change_date<='2019-08-16')

select product_id , new_price as price from newt 
where rank = 1
union 
select product_id ,10 from products
where product_id not in (select distinct product_id from newt)


--------------------------------------- solution 2 ------------------------------


SELECT DISTINCT p1.product_id, COALESCE(p2.new_price,10) price
FROM products p1 LEFT JOIN (SELECT product_id, new_price, rank() OVER(PARTITION BY product_id ORDER BY change_date DESC) rnk
                            FROM products
                            WHERE change_date<='2019-08-16') p2
                ON p1.product_id=p2.product_id AND rnk=1;