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 vouchers (
id SERIAL PRIMARY KEY,
customer VARCHAR,
collected_date DATE,
balance INT
);
INSERT INTO vouchers
(customer, collected_date, balance
)
VALUES
('customer_a', '2024-03-09', '1600'),
('customer_a', '2024-04-20', '2700'),
('customer_a', '2024-05-24', '3100'),
('customer_a', '2025-03-09', '1500'),
('customer_a', '2025-04-20', '400'),
('customer_a', '2025-05-24', '0'),
('customer_b', '2024-10-17', '200'),
('customer_b', '2025-02-15', '1000'),
('customer_b', '2025-10-17', '800'),
('customer_b', '2025-11-18', '950'),
('customer_b', '2026-02-15', '1150'),
('customer_b', '2026-11-18', '0');
CREATE TABLE
INSERT 0 12
select
customer as customer,
collected_date as collected_date,
sum(balance) as balance,
max(collected_date) over (partition by customer) AS max_date
from vouchers
where collected_date < '2025-03-31'
group by 1,2
order by 1,2 desc;
| customer | collected_date | balance | max_date |
|---|---|---|---|
| customer_a | 2025-03-09 | 1500 | 2025-03-09 |
| customer_a | 2024-05-24 | 3100 | 2025-03-09 |
| customer_a | 2024-04-20 | 2700 | 2025-03-09 |
| customer_a | 2024-03-09 | 1600 | 2025-03-09 |
| customer_b | 2025-02-15 | 1000 | 2025-02-15 |
| customer_b | 2024-10-17 | 200 | 2025-02-15 |
SELECT 6