CODEWITHSUNDEEP
databasepostgresqlreport
nadegnaro
nadegnaro

Reputation: 45

Compare value between current date and yesterday on the same table POSTGRESQL

I have a table like this:

product value trx_date
apple 100 2020-06-01
apple 300 2020-06-02
apple 500 2020-06-03

and I need create a report like this (let's say today is 2020-06-03)

product yesterday current_date delta
apple 300 500 200

I'm confused how to create a query (PostgreSQL), comparing those values. fyi, i always update this table every day. I tried with ('1 day'::interval) query but it always shows all dates before 2020-06-03 which is 2020-06-01 and 2020-06-02...

Upvotes: 3

Views: 1250

Answers (2)

Belayer
Belayer

Reputation: 14861

Use the Window Function lead or lag to 'combine' data to the current row from following rows (lead) or previous rows (lag). In this case the I use the lag function to get "yesterdays" value. 

select product, yesterday,  today, today-yesterday delta
  from ( select p.product, p.value today 
              , lag(value) over (partition by p.product 
                                     order by p.trx_date) yesterday
              , p.trx_date
          from products p
       ) d
 where trx_date = '2020-06-03'::date ;

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19665

Using CTE:

https://www.postgresql.org/docs/12/queries-with.html

An example:

CREATE TABLE product_table (product varchar, value integer, trx_date date);
INSERT INTO product_table values ('apple', 100, '06/01/2020'), ('apple', 300, '06/02/2020'), ('apple', 500, '06/03/2020');

WITH prev AS (
    SELECT
        product,
        value
    FROM
        product_table
    WHERE
        trx_date = '06/03/2020'::date - '1 day'::interval
)
SELECT
    pt.product,
    prev.value AS yesterday,
    pt.value AS CURRENT_DATE,
    pt.value - prev.value AS delta
FROM
    product_table AS pt,
    prev
WHERE
    trx_date = '06/03/2020';

 product | yesterday | current_date | delta 
---------+-----------+--------------+-------
 apple   |       300 |          500 |   200

Upvotes: 0

Related Questions