1164 Product Price at a Given Date

Description

Table: 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.

Problem Statement

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:

• Products table:

Output:

Solution

The problem is asking to find the prcie for each product on 2019-08-16. That means we can ignore all changes after this date.

1SELECT product_id, change_date, new_price
2    FROM Products 
3    WHERE change_date <= '2019-08-16'
4    ORDER BY product_id, change_date

Out of these prices for product_id=1, you need the first value when ordered by change_date in descending order for each product_id.

1SELECT product_id,
2    FIRST_VALUE(new_price) OVER (PARTITION BY product_id ORDER BY change_date DESC)
3    FROM Products 
4    WHERE change_date <= '2019-08-16'
5    ORDER BY product_id, change_date

The next step is step is to fetch only distinct products from the result using DISTINCT keyword.

1SELECT DISTINCT product_id,
2    FIRST_VALUE(new_price) OVER (PARTITION BY product_id ORDER BY change_date DESC) AS new_price
3    FROM Products 
4    WHERE change_date <= '2019-08-16';

Next, you need to report prices for all products. Remember that if a product’s price is never change, it had a default value of 10. So, you still need to output price based on that.

 1WITH changed_products AS (
 2    SELECT DISTINCT product_id,
 3        FIRST_VALUE(new_price) OVER (PARTITION BY product_id ORDER BY change_date DESC) AS price
 4        FROM Products 
 5        WHERE change_date <= '2019-08-16'
 6) SELECT p.product_id,
 7    CASE WHEN cp.price IS NULL THEN 10 ELSE cp.price END AS price
 8    FROM (SELECT DISTINCT product_id FROM Products) p
 9    LEFT OUTER JOIN changed_products cp
10    ON p.product_id = cp.product_id;