SQL Server Query to find the prices of all products on 2019-08-16

wf82jlnq  于 2023-06-28  发布在  其他
关注(0)|答案(3)|浏览(91)

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 desc

And 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
kb5ga3dv

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.

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
         , ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
    FROM products
) t
WHERE t.rk = 1;
jq6vz3qz

jq6vz3qz2#

You can use a correlated subquery:

select p.*
from products p
where p.date = (select max(p2.date)
                from products p2
                where p2.product_id = p.product_id and
                      p2.date <= '2019-08-16'
               );
roejwanj

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.

相关问题