Get products which had a price update last week-CodePudding

This is my table History:

productnr    price     changedate
1001           5         06.05.2020
1001           9         01.10.2021
1001           10        08.10.2021
1002           6         01.04.2021
1002           7         14.05.2021
1002           14        07.10.2021

I need to have every product which its price change last week AND the difference between last price and new price is more than 15 percent.

The desired result:

productnr     newprice    oldprice       last_changedate    secondlast_changedate 
1002          14            7                07.10.2021      14.05.2021

With this SQL query I have all products which their price changed last week :

Select *
from history
where TO_CHAR(changedate, 'iw') = TO_CHAR(next_day(trunc(sysdate 2), 'MONDAY') - 14, 'iw')
      and changedate > sysdate - 14

But I have no idea how can reach the desired result.

CodePudding user response：

You can use the analytic functions to find the previous values:

If you want to compare the latest productnr to the previous:

SELECT *
FROM   (
  SELECT h.*,
         LEAD(price     ) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldprice,
         LEAD(changedate) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldchangedate,
         ROW_NUMBER() OVER (PARTITION BY productnr ORDER BY changedate DESC) AS rn
  FROM   history h
  WHERE  changedate > TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
)
WHERE  (price < oldprice * (1 - 0.15) OR price > oldprice * (1   0.15))
AND    rn = 1;

or, if you want to compare the first price per productnr this week to the last price last week:

SELECT *
FROM   (
  SELECT h.*,
         LEAD(price     ) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldprice,
         LEAD(changedate) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldchangedate
  FROM   history h
  WHERE  changedate >= TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
  AND    changedate <  TRUNC(SYSDATE, 'IW')   INTERVAL '7' DAY
)
WHERE  (price < oldprice * (1 - 0.15) OR price > oldprice * (1   0.15))
AND    changedate    >= TRUNC(SYSDATE, 'IW')
AND    oldchangedate <  TRUNC(SYSDATE, 'IW');