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 | +------------+-------+
# Write your MySQL query statement below
SELECT
p1.product_id AS product_id,
IFNULL(p2.price, 10) AS price
FROM
(
SELECT DISTINCT
(product_id) AS product_id
FROM Products
) AS p1
LEFT JOIN (
SELECT
t1.product_id,
t1.new_price AS price
FROM
Products AS t1
JOIN (
SELECT
product_id,
MAX(change_date) AS change_date
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY product_id
) AS t2
ON t1.product_id = t2.product_id AND t1.change_date = t2.change_date
) AS p2
ON p1.product_id = p2.product_id;# Write your MySQL query statement below
WITH
P AS (
SELECT p1.product_id, new_price, change_date
FROM
(
SELECT DISTINCT product_id
FROM Products
) AS p1
LEFT JOIN Products AS p2
ON p1.product_id = p2.product_id AND p2.change_date <= '2019-08-16'
),
T AS (
SELECT
*,
RANK() OVER (
PARTITION BY product_id
ORDER BY change_date DESC
) AS rk
FROM P
)
SELECT product_id, IFNULL(new_price, 10) AS price
FROM T
WHERE rk = 1;