I have a db table with a bunch of records in a snapshot fashioned way, i. e. daily captures of product units availability for many years
product units category expire_date report_date
pineapple 10 common 12/25/2021 12/01/2021
pineapple 8 common 12/25/2021 12/02/2021
pineapple 8 deluxe 12/28/2021 12/02/2021
grapes 45 deluxe 11/30/2022 12/01/2021
...
pineapple 21 common 12/12/2022 12/01/2022
...
What I'm trying to get from that data is something like this "lagged" version, partitioning by product and category:
product units category report_date prev_year_units_atreportdate
pineapple 10 common 12/01/2021 NULL
pineapple 21 common 12/01/2022 10
pineapple 16 common 12/01/2023 21
...
It's important to know that from time to time the cron snapshot task fails and no records are stored for days. This leads to a different number of records by product.
I've been using LAG() to no avail since I can only get previous day/month using partitioning by product, category
Can anyone help me on this?
I think I would use a subselect rather than a window function.
select *,
(
select units from t t2
where t2.report_date=t1.report_date-interval '1 year' and t2.product=t1.product and t2.category=t1.category
) lagged_units
from t as t1
I'm not sure what you want to happen on leap year, though, or the year after one.