SQL Interview Questions – Product Price at a Given Date

Problem Description –

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.

Difficulty Level – Medium

Problem Link – Product Price

Solution –

with result as (
SELECT
    product_id,
    new_price,
    rank() Over(partition by product_id order by change_date desc) as rnk
from products
where change_date <= '2019-08-16'
) 

SELECT 
    DISTINCT p.product_id, 
    COALESCE(temp.new_price, 10) as price
FROM Products as p LEFT JOIN (SELECT * FROM result WHERE rnk=1) as temp
ON p.product_id = temp.product_id

In the common table expression, first we filter the data to include only the records which are less than or equal to 2019-08-16 because we were asked to find the product price at that given date. Then we use the RANK window function. We partition by product_id and order the data by change_date in descending order to get the latest price for each product which can be seen in the following query.

with result as (
SELECT
    product_id,
    new_price,
    rank() Over(partition by product_id order by change_date desc) as rnk
from products
where change_date <= '2019-08-16'
) 
SELECT * FROM result;

The we simply join this result with the product table and if there is a null value we replace it with 10 as it is given that the price of all products before any change is 10.