Find price changes over time-CodePudding

I have a table with these columns: id, sku, price, date

CREATE TABLE price_log (
  id int NOT NULL  PRIMARY KEY,
  sku text NOT NULL,
  date date NOT NULL,
  price real NOT NULL,
  CONSTRAINT date_sku UNIQUE (sku,date)
)

I want to get a report of change price in overtime.

My desired output is:

sku     old_price    new_price    change_date
A          10           11         2022-01-03
B          1            5.99       2022-01-02
B          5.99         3.5        2022-01-03
B          3.5          12         2022-01-04

Right now the best I could do is to use LAG() function:

WITH cte AS (
 SELECT sku, price, MAX(date) AS date
 FROM price_log
 GROUP BY sku, price
)
SELECT sku, price, date,
LAG(price,1) OVER (
    PARTITION BY sku
    ORDER BY date
) AS old_price
FROM cte

dbfiddle

CodePudding user response：

select sku
      ,old_price
      ,new_price
      ,change_date
from   (
       select sku
             ,lag(price) over(partition by sku order by date) as old_price
             ,price                                           as new_price
             ,date                                            as change_date
       from   price_log
       )      price_log
where  old_price <> new_price

sku	old_price	new_price	change_date
A	10	11	2022-01-03
B	1	5.99	2022-01-02
B	5.99	3.5	2022-01-03
B	3.5	12	2022-01-04

Fiddle

CodePudding user response：

Try the following:

WITH CTE AS
(
  SELECT sku, price, date,
         LAG(price,1,price) OVER (PARTITION BY sku ORDER BY date) AS old_price
  FROM price_log
)
SELECT sku, old_price, price AS new_price, date AS change_date 
FROM CTE 
WHERE price <> old_price

See a demo.

CodePudding user response：

There are N ways to do that. For example one would be to use lateral:

select p1.sku, t.old_price, p1.price as new_price, p1.date as change_Date
from price_log p1, 
lateral (select price from price_log p2 
         where p1.sku = p2.sku and
               p1.date > p2.date
         order by date desc
         limit 1) t(old_price);

DbFiddle demo