1164 Product Price at a Given Date

1164. Product Price at a Given Date: Learn how to track and select price from continuously changing series. Read more

Company: MAANG

Difficulty: Medium

Source: LeetCode

SQL Interview Question: 1164. Product Price at a Given Date

Question.

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.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(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.

Input: 
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  |
+------------+-----------+-------------+
Output: 
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

-- Create the products table
CREATE TABLE products (
    product_id INT NOT NULL,
    new_price DECIMAL(10, 2) NOT NULL,
    change_date DATE NOT NULL,
    PRIMARY KEY (product_id, change_date)
);

-- Insert data into the products table
INSERT INTO products (product_id, new_price, change_date)
VALUES 
    (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');

Solution

SELECT DISTINCT (p.product_id), IFNULL(p2.price, 10) AS price
FROM products p
LEFT JOIN (
    SELECT product_id, new_price AS price
    FROM products
    WHERE (product_id, change_date) IN (
               SELECT product_id, MAX(change_date)
               FROM products
               WHERE change_date <= '2019-08-16'
               GROUP BY product_id
    )
) AS p2 
ON p.product_id = p2.product_id;

Explanation

The task is to determine the price of each product as of a specific date (2019-08-16). If no price change exists before or on this date for a product, a default price of 10 is assigned. This query solves the problem using a LEFT JOIN. Let’s break it down step-by-step from the innermost query outward.

Step 1: Find the Latest Price Change Date for Each Product

SELECT product_id, MAX(change_date)
FROM products
WHERE change_date <= '2019-08-16'
GROUP BY product_id

product_id		change_date
1		2019-08-15
2		2019-08-12

Step 2: Match the Latest Change Date with the Corresponding Price

SELECT product_id, new_price AS price
FROM products
WHERE (product_id, change_date) IN (
    SELECT product_id, MAX(change_date)
    FROM products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
)

product_id	price
1	35
2	50

Step 3: Retrieve All Products and Combine with Prices

SELECT DISTINCT (p.product_id), IFNULL(p2.price, 10) AS price
FROM products p
LEFT JOIN (
        SELECT product_id, new_price AS price
        FROM products
        WHERE (product_id, change_date) IN (
                SELECT product_id, MAX(change_date)
                FROM products
                WHERE change_date <= '2019-08-16'
                GROUP BY product_id
            )
) AS p2 
ON p.product_id = p2.product_id;

product_id	price
1	50
2	35
3	10

---

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

• Write a query to identify the employee(s) whose salary is closest to the average salary of the company.
• Display all months where sales exceeded the average monthly sales.
• Find the most common value (mode) in a specific column.
• Identify employees who have never received a performance review.
• Write a query to retrieve the first order placed by each customer.