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

Question

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

Answer 1

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 ;