Getting an approximate previous year's value

user_5535404 0
Viewed 58

I have a table that has the following sample rows:

Product Date        Revenue
A       2021-05-10  20
A       2021-03-20  10
A       2020-01-10  5
A       2020-03-10  6
A       2020-04-10  7

For each product and date, I'd like to get the closest date to last year from its original date. For example, the first row's date is 2021-05-10 the closest to the previous year for this date is 2020-04-10. The resulting output I'd like is the following:

Product Date        Revenue   PrevDate  PrevRevenue
A       2021-05-10  20        2020-04-10   7
A       2021-03-20  10        2020-03-10   6
A       2020-01-10  5         null         null
A       2020-03-10  6         null         null
A       2020-04-10  7         null         null

How would I achieve something like this?

postgresql sql
2 Answers

One method uses a lateral join:

select t1.*, t2.*
from t t1 left join lateral
     (select t2.*
      from t t2
      where t2.product = t1.product and
            t2.date < dateadd(month, -6, t1.date)
      order by abs(t2.date - (t1.date - interval '1 year'))
     ) t2
     on 1=1;
user_11440350

A slightly different query than the one suggest by Gordon:

You could use the LEFT LATERAL JOIN to filter the closest date from a previous year from the current date, by subtracting a 1 year interval and ordering by it with a LIMIT 1:

SELECT t1.*,t2.prevdate,t2.prevrevenue FROM t t1
LEFT JOIN LATERAL 
  (SELECT product,date,revenue FROM t t2
   WHERE t2.date < t1.date-interval'1 year' AND t1.product = t2.product
   ORDER BY abs(date_part('day',(t1.date-interval' 1year')-t2.date))   
   LIMIT 1) t2 (product,prevdate,prevrevenue) USING (product);

Demo: db<>fiddle

user_22753880
Related