skip to Main Content

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

3

Answers


  1. 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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search