Question 114815 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find price changes over time

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

Tags:

3

Answers


  1. 0

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

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