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 IF NOT EXISTS item_ (
id SERIAL PRIMARY KEY,
transaction_date DATE NOT NULL,
item_name TEXT NOT NULL,
transaction_type TEXT NOT NULL,
amount NUMERIC NOT NULL)
INSERT INTO item_ VALUES
(1,'2021-03-18','A','sell',4)
,(2,'2021-03-18','B','sell',84)
,(3,'2021-03-21','A','buy',23)
,(4,'2021-03-23','A','sell',68)
,(5,'2021-03-26','B','buy',46)
,(6,'2021-03-26','A','buy',32)
,(7,'2021-03-29','A','buy',53)
,(8,'2021-04-01','A','sell',41)
,(9,'2021-04-01','B','sell',97)
,(10,'2021-04-07','B','sell',94)
,(11,'2021-04-10','A','sell',76)
11 rows affected
CREATE TABLE IF NOT EXISTS date (
date_dim_id SERIAL PRIMARY KEY,
date_actual DATE NOT NULL)
INSERT INTO date VALUES
('20210410','2021-04-10')
,('20210409','2021-04-09')
,('20210408','2021-04-08')
,('20210407','2021-04-07')
,('20210406','2021-04-06')
,('20210405','2021-04-05')
,('20210404','2021-04-04')
,('20210403','2021-04-03')
,('20210402','2021-04-02')
,('20210401','2021-04-01')
,('20210331','2021-03-31')
,('20210330','2021-03-30')
,('20210329','2021-03-29')
,('20210328','2021-03-28')
,('20210327','2021-03-27')
,('20210326','2021-03-26')
,('20210325','2021-03-25')
,('20210324','2021-03-24')
,('20210323','2021-03-23')
,('20210322','2021-03-22')
,('20210321','2021-03-21')
,('20210320','2021-03-20')
,('20210319','2021-03-19')
,('20210318','2021-03-18')
,('20210317','2021-03-17')
,('20210316','2021-03-16')
,('20210315','2021-03-15')
,('20210314','2021-03-14')
,('20210313','2021-03-13')
,('20210312','2021-03-12')
,('20210311','2021-03-11')
,('20210310','2021-03-10')
,('20210309','2021-03-09')
,('20210308','2021-03-08')
,('20210307','2021-03-07')
45 rows affected
select date_actual,transaction_date
FROM date LEFT JOIN item_ ON item_.transaction_date=date.date_actual
ORDER BY date.date_actual DESC
| date_actual | transaction_date |
|---|---|
| 2021-04-10 | 2021-04-10 |
| 2021-04-09 | null |
| 2021-04-08 | null |
| 2021-04-07 | 2021-04-07 |
| 2021-04-06 | null |
| 2021-04-05 | null |
| 2021-04-04 | null |
| 2021-04-03 | null |
| 2021-04-02 | null |
| 2021-04-01 | 2021-04-01 |
| 2021-04-01 | 2021-04-01 |
| 2021-03-31 | null |
| 2021-03-30 | null |
| 2021-03-29 | 2021-03-29 |
| 2021-03-28 | null |
| 2021-03-27 | null |
| 2021-03-26 | 2021-03-26 |
| 2021-03-26 | 2021-03-26 |
| 2021-03-25 | null |
| 2021-03-24 | null |
| 2021-03-23 | 2021-03-23 |
| 2021-03-22 | null |
| 2021-03-21 | 2021-03-21 |
| 2021-03-20 | null |
| 2021-03-19 | null |
| 2021-03-18 | 2021-03-18 |
| 2021-03-18 | 2021-03-18 |
| 2021-03-17 | null |
| 2021-03-16 | null |
| 2021-03-15 | null |
| 2021-03-14 | null |
| 2021-03-13 | null |
| 2021-03-12 | null |
| 2021-03-11 | null |
| 2021-03-10 | null |
| 2021-03-09 | null |
| 2021-03-08 | null |
| 2021-03-07 | null |
| 2021-03-06 | null |
| 2021-03-05 | null |
| 2021-03-04 | null |
| 2021-03-03 | null |
| 2021-03-02 | null |
| 2021-03-01 | null |
| 2021-02-28 | null |
| 2021-02-27 | null |
| 2021-02-26 | null |
| 2021-02-25 | null |
SELECT
t1.Id
,t1.transaction_date
,t1.amount
,SUM(t2.amount) as Rolling30DayQt
FROM item_ t1
CROSS JOIN(
SELECT *
FROM item_ a
WHERE a.id=t1.Id
AND (a.transaction_date > DATEADD(DAY,-30,t1.transaction_date) AND a.transaction_date <= t1.transaction_date) ) t2
GROUP BY t1.Id ,t1.transaction_date ,t1.amount
ERROR: invalid reference to FROM-clause entry for table "t1"
LINE 10: WHERE a.id=t1.Id
^
HINT: There is an entry for table "t1", but it cannot be referenced from this part of the query.