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

Compare today's value with yesterday's value and return data if difference is > 10 – Postgresql

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

eff_date product_id price
2022-11-25 P1 150
2022-11-25 P2 75.8
2022-11-25 P3 2.9
2022-11-26 P1 180.5
2022-11-26 P2 77
2022-11-26 P4 13.92

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:

eff_date product_id todays_price yesterdays_price
2022-11-26 P1 180.5 150
Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    I have come up with the below query. Can someone please confirm if it's 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

  2. 0

    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
    eff_date product_id today’s price yesterday’s price
    2022-11-28 P1 180.5 150
    2022-11-28 P5 150 180.5 
    SELECT 2

    fiddle

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
  4. 0

    Try this:

    create temporary table temp1 as
    select 
    eff_date,
    product_id,
    price as todays_price
    lag(price) over(partition by product_id order by eff_date) as yesterdays_price,
    from products
    
    select * from temp1
    where 
    (todays_price-yesterdays_price)/yesterdays_price >= 0.10

    Note that we are only including only those products whose price increased 10%

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search