1164. Product Price at a Given Date

Description of Problem

Table: Products

+---------------+---------+
| 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.

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:
+------------+-----------+-------------+
| 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    |
+------------+-------+

Solution

Tags: SQL Grouping Divide and Conquer

Code (MySQL)

-- Find all prices where change_date is before '2019-08-16'. 
-- GROUP BY product_id and ORDER BY change_date
WITH p AS (
    SELECT 
        ROW_NUMBER() OVER (
            PARTITION BY p2.product_id 
            ORDER BY p2.change_date DESC
        ) row_num,
        p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
)

-- Find the latest record
SELECT p.product_id, p.new_price AS price
FROM p
WHERE row_num = 1

UNION

-- Find products where change_date is after '2019-08-16'
SELECT p1.product_id, 10 AS price
FROM Products AS p1
WHERE NOT EXISTS (
    SELECT 1 
    FROM p AS p2 
    WHERE p2.product_id = p1.product_id
);