I have a Products table where prices of products are updated every day.

But sometimes not all products will have data for each date (like how p3 do not have data for 26th and p4 do not have data for 25th).

Consider today's date is 26th then I want to compare today's price with yesterday's price and if difference is > 10% (price increased by 10% or more )then I want output like below:

CodePudding user response：

Adapting your date to the current_date, you can do like Lamun said.

A CTE gets you all the data you need

CREATE TABLE products
    ("eff_date" date, "product_id" varchar(2), "price" numeric)
;
    
INSERT INTO products
    ("eff_date", "product_id", "price")
VALUES
    ('2022-11-27', 'P1', 150),
    ('2022-11-27', 'P2', 75.8),
    ('2022-11-27', 'P3', 2.9),
    ('2022-11-28', 'P1', 180.5),
    ('2022-11-28', 'P2', 77),
    ('2022-11-28', 'P4', 13.92),
  ('2022-11-28', 'P5', 150),
  ('2022-11-27', 'P5', 180.5)
;

CREATE TABLE

INSERT 0 8

WITH CTE as (
SELECT
    "eff_date", "product_id", "price"
    ,LAG("price") OVER(PARTITION BY "product_id" ORDER BY "eff_date") last_price
, extract(day from "eff_date"::timestamp - LAG("eff_date") OVER(PARTITION BY "product_id" ORDER BY "eff_date")::timestamp ) diffdays
FROM 
  products)
SELECT "eff_date", "product_id" , "price" as "today's price" , last_price as "yesterday's price"
   FROm CTE
WHERE diffdays = 1 AND "eff_date" = current_date AND  ABS( ( 1 - "price"/ last_price)) > 0.1 

SELECT 2

fiddle

CodePudding user response：

You can use a CTE which groups your data based on product_id and sorts by the date, then check if the current price >= previous price *1.1 to cover your condition the price must have increased by at least 10%.

WITH CTE AS (
SELECT
    eff_date, 
    product_id,
    price,
    LAG(eff_date,1) OVER (PARTITION BY product_id
        ORDER BY eff_date
    ) previous_date,
    LAG(price,1) OVER (PARTITION BY product_id
        ORDER BY eff_date
    ) previous_price
FROM products)
SELECT eff_date, product_id, 
price AS todays_price, 
previous_price AS yesterdays_price
FROM cte
WHERE 
eff_date = previous_date   1 AND 
price >= previous_price * 1.1;

Try out: db<>fiddle

CodePudding user response：

I have come up with below query. Can someone please confirm if its correct.

select a.eff_date as todays_date,b.eff_date as yesterdays_date, a.product_id, a.price as todays_price, b.price as yesterdays_price, (((a.price - b.price) / b.price) * 100) as perc_change

from (select * from products where eff_date=cast(CURRENT_DATE as date)) a

inner join

(select * from products where eff_date=cast(CURRENT_DATE -1 as date)) b

on a.product_id=b.product_id

where (((a.price - b.price) / b.price) * 100) > 10