By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t (product text, date date,revenue int);
INSERT INTO t VALUES
('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);
5 rows affected
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);
| 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 |