Postgres 13

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 (client_id int, balance_month timestamp, balance int);
    
INSERT INTO tbl VALUES
 (1, '2021-09-01 02:00:00', '7000'),
 (1, '2021-08-01 02:00:00', '7000'),
 (1, '2021-07-01 02:00:00', '6000'),
 (1, '2021-06-01 02:00:00', '3000'),
 (1, '2021-05-01 02:00:00', '20000'),
 (1, '2021-04-01 02:00:00', '1000'),
 (1, '2021-03-01 01:00:00', '0'),
 (2, '2021-09-01 02:00:00', '7000'),
 (2, '2021-08-01 02:00:00', '7000'),
 (2, '2021-07-01 02:00:00', '8000'),
 (2, '2021-06-01 02:00:00', '9000'),
 (2, '2021-05-01 02:00:00', '1000'),
 (2, '2021-04-01 02:00:00', '1000'),
 (2, '2021-03-01 01:00:00', '0'),
 -- add another client where the balance falls below previous maximum:
 (3, '2021-09-01 02:00:00', '6000'),
 (3, '2021-08-01 02:00:00', '7000'),
 (3, '2021-07-01 02:00:00', '1000'),  -- fall below previous max
 (3, '2021-06-01 02:00:00', '9000'),
 (3, '2021-05-01 02:00:00', '2000'),
 (3, '2021-04-01 02:00:00', '1000'),
 (3, '2021-03-01 01:00:00', '0');
21 rows affected

SELECT *
     , first_value(balance_null) OVER (PARTITION BY client_id, balance_grp ORDER BY balance_month) AS new_balance
FROM  (
   SELECT *
        , count(balance_null) OVER (PARTITION BY client_id ORDER BY balance_month) AS balance_grp
   FROM  (
      SELECT client_id, balance_month, balance
           , CASE WHEN lead(balance) OVER (PARTITION BY client_id ORDER BY balance_month) < balance THEN NULL ELSE balance END AS balance_null
      FROM   tbl
      ORDER  BY client_id, balance_month
      ) sub1
   ) sub2;


client_id
balance_month
balance
balance_null
balance_grp
new_balance


1
2021-03-01 01:00:00
0
0
1
0

1
2021-04-01 02:00:00
1000
1000
2
1000

1
2021-05-01 02:00:00
20000
null
2
1000

1
2021-06-01 02:00:00
3000
3000
3
3000

1
2021-07-01 02:00:00
6000
6000
4
6000

1
2021-08-01 02:00:00
7000
7000
5
7000

1
2021-09-01 02:00:00
7000
7000
6
7000

2
2021-03-01 01:00:00
0
0
1
0

2
2021-04-01 02:00:00
1000
1000
2
1000

2
2021-05-01 02:00:00
1000
1000
3
1000

2
2021-06-01 02:00:00
9000
null
3
1000

2
2021-07-01 02:00:00
8000
null
3
1000

2
2021-08-01 02:00:00
7000
7000
4
7000

2
2021-09-01 02:00:00
7000
7000
5
7000

3
2021-03-01 01:00:00
0
0
1
0

3
2021-04-01 02:00:00
1000
1000
2
1000

3
2021-05-01 02:00:00
2000
2000
3
2000

3
2021-06-01 02:00:00
9000
null
3
2000

3
2021-07-01 02:00:00
1000
1000
4
1000

3
2021-08-01 02:00:00
7000
null
4
1000

3
2021-09-01 02:00:00
6000
6000
5
6000


-- Note the difference for client 3
SELECT *
     , COALESCE(balance_null, max(balance_null) OVER (PARTITION BY client_id ORDER BY balance_month)) AS new_balance
FROM  (
   SELECT client_id, balance_month, balance
        , CASE WHEN lead(balance) OVER (PARTITION BY client_id ORDER BY balance_month) < balance THEN NULL ELSE balance END AS balance_null
   FROM   tbl
   ORDER  BY client_id, balance_month
   ) sub;


client_id
balance_month
balance
balance_null
new_balance


1
2021-03-01 01:00:00
0
0
0

1
2021-04-01 02:00:00
1000
1000
1000

1
2021-05-01 02:00:00
20000
null
1000

1
2021-06-01 02:00:00
3000
3000
3000

1
2021-07-01 02:00:00
6000
6000
6000

1
2021-08-01 02:00:00
7000
7000
7000

1
2021-09-01 02:00:00
7000
7000
7000

2
2021-03-01 01:00:00
0
0
0

2
2021-04-01 02:00:00
1000
1000
1000

2
2021-05-01 02:00:00
1000
1000
1000

2
2021-06-01 02:00:00
9000
null
1000

2
2021-07-01 02:00:00
8000
null
1000

2
2021-08-01 02:00:00
7000
7000
7000

2
2021-09-01 02:00:00
7000
7000
7000

3
2021-03-01 01:00:00
0
0
0

3
2021-04-01 02:00:00
1000
1000
1000

3
2021-05-01 02:00:00
2000
2000
2000

3
2021-06-01 02:00:00
9000
null
2000

3
2021-07-01 02:00:00
1000
1000
1000

3
2021-08-01 02:00:00
7000
null
2000

3
2021-09-01 02:00:00
6000
6000
6000

client_id	balance_month	balance	balance_null	balance_grp	new_balance
1	2021-03-01 01:00:00	0	0	1	0
1	2021-04-01 02:00:00	1000	1000	2	1000
1	2021-05-01 02:00:00	20000	null	2	1000
1	2021-06-01 02:00:00	3000	3000	3	3000
1	2021-07-01 02:00:00	6000	6000	4	6000
1	2021-08-01 02:00:00	7000	7000	5	7000
1	2021-09-01 02:00:00	7000	7000	6	7000
2	2021-03-01 01:00:00	0	0	1	0
2	2021-04-01 02:00:00	1000	1000	2	1000
2	2021-05-01 02:00:00	1000	1000	3	1000
2	2021-06-01 02:00:00	9000	null	3	1000
2	2021-07-01 02:00:00	8000	null	3	1000
2	2021-08-01 02:00:00	7000	7000	4	7000
2	2021-09-01 02:00:00	7000	7000	5	7000
3	2021-03-01 01:00:00	0	0	1	0
3	2021-04-01 02:00:00	1000	1000	2	1000
3	2021-05-01 02:00:00	2000	2000	3	2000
3	2021-06-01 02:00:00	9000	null	3	2000
3	2021-07-01 02:00:00	1000	1000	4	1000
3	2021-08-01 02:00:00	7000	null	4	1000
3	2021-09-01 02:00:00	6000	6000	5	6000