I'm doing the following exercise:
Question 67
Table: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+(product_id, change_date) is the primary key 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 an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10. The query result format is in the following example: 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 |
+------------+-----------+-------------+Result table:
+------------+-------+
| product_id | price |
+------------+-------+
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
+------------+-------+This is the solution given by other people:
-- Solution
with t1 as (
select a.product_id, new_price
from(
Select product_id, max(change_date) as date
from products
where change_date<='2019-08-16'
group by product_id) a
join products p
on a.product_id = p.product_id and a.date = p.change_date),
t2 as (
select distinct product_id
from products)
select t2.product_id, coalesce(new_price,10) as price
from t2 left join t1
on t2.product_id = t1.product_id
order by price descAnd here is my solution. I tried to find others solutions on the internet, but all of them are in a very complicated way, why nobody did it this way. Could you please let me know if there is anything wrong with my solution?
SELECT
t.product_id
,CASE
WHEN t.change_date <= '2019-08-16' THEN t.new_price
ELSE 10
END AS price
FROM
(
SELECT
product_id
,new_price
,change_date
,RANK() OVER
(
PARTITION BY product_id
ORDER BY change_date DESC) AS rk
FROM products
) t
WHERE t.rk = 1
3条答案
按热度按时间kb5ga3dv1#
Your solution below (I let SQLPrompt format it for readability). Your solution works. And works well. I use the exact same technique regularly. I recommend using ROW_NUMBER() instead of RANK() because it is always possible to get 2 identical rows -- at least it happened to me enough to cause me to change. But your original solution works well.
jq6vz3qz2#
You can use a correlated subquery:
roejwanj3#
The problem with your solution is when you select rows with rank 1, you always consider the max change date for each product. But, if you see product 2, the price was increased on 2019-08-14 which was 50. So, the price on 2019-08-16 should be 50 because the next change was made on 2019-08-17 to 65. When you filter with rank=1, you check that the date 17 is greater than date 16 and it sets the price to 10 which is not true in this case. Hence, your solution is not correct.