| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
Medium |
|
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 | +------------+-------+
We can use a subquery to find the price of the last price change for each product before the given date, and record it in the P table. Then, we can find all product_ids in the T table. Finally, we can left join the T table with the P table on product_id to get the final result.
# Write your MySQL query statement below
WITH
T AS (SELECT DISTINCT product_id FROM Products),
P AS (
SELECT product_id, new_price AS price
FROM Products
WHERE
(product_id, change_date) IN (
SELECT product_id, MAX(change_date) AS change_date
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY 1
)
)
SELECT product_id, IFNULL(price, 10) AS price
FROM
T
LEFT JOIN P USING (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;