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 tbl (
month_rep date
, fruits int
, harvested int
);
INSERT INTO tbl VALUES
('2021-09-01', 139, 139)
, ('2021-10-01', 143, 11)
, ('2021-11-01', 152, 14)
, ('2021-12-01', 112, 9)
, ('2022-01-01', 133, 10)
, ('2022-02-01', 145, 12)
, ('2022-03-01', 123, 5)
, ('2022-04-01', 111, 4)
, ('2022-05-01', 164, 9)
, ('2022-06-01', 135, 12)
, ('2022-07-01', 124, 14)
, ('2022-08-01', 144, 18)
, ('2022-09-01', 111, 111)
, ('2022-10-01', 108, 13)
, ('2022-11-01', 123, 7)
, ('2022-12-01', 132, 20)
;
CREATE TABLE
INSERT 0 16
SELECT *
, CASE extract(month FROM month_rep)
WHEN 7 THEN NULL
WHEN 8 THEN NULL
WHEN 9 THEN 1
ELSE round(fruits::numeric / sum(harvested) OVER (PARTITION BY date_trunc('year', month_rep - interval '8 mon') ORDER BY month_rep), 2)
END AS sold
FROM tbl
ORDER BY month_rep;
| month_rep | fruits | harvested | sold |
|---|---|---|---|
| 2021-09-01 | 139 | 139 | 1 |
| 2021-10-01 | 143 | 11 | 0.95 |
| 2021-11-01 | 152 | 14 | 0.93 |
| 2021-12-01 | 112 | 9 | 0.65 |
| 2022-01-01 | 133 | 10 | 0.73 |
| 2022-02-01 | 145 | 12 | 0.74 |
| 2022-03-01 | 123 | 5 | 0.62 |
| 2022-04-01 | 111 | 4 | 0.54 |
| 2022-05-01 | 164 | 9 | 0.77 |
| 2022-06-01 | 135 | 12 | 0.60 |
| 2022-07-01 | 124 | 14 | null |
| 2022-08-01 | 144 | 18 | null |
| 2022-09-01 | 111 | 111 | 1 |
| 2022-10-01 | 108 | 13 | 0.87 |
| 2022-11-01 | 123 | 7 | 0.94 |
| 2022-12-01 | 132 | 20 | 0.87 |
SELECT 16