Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (client_id INTEGER, balance_month DATE, balance INTEGER);

INSERT INTO tablename (client_id, balance_month, balance) VALUES

('100000000', 'September 1,2021 12:00 AM', '400'),

('100000000', 'August 1,2021 12:00 AM', '100'),

('100000000', 'July 1,2021 12:00 AM', '200'),

('100000000', 'June 1,2021 12:00 AM', '300'),

('200000000', 'September 1,2021 12:00 AM', '99'),

('200000000', 'August 1,2021 12:00 AM', '100'),

('200000000', 'July 1,2021 12:00 AM', '100'),

('200000000', 'June 1,2021 12:00 AM', '100');

8 rows affected

SELECT client_id, balance_month,

CASE

WHEN balance_month <

MAX(CASE WHEN prev_balance > balance THEN balance_month END) OVER (PARTITION BY client_id)

THEN 0

ELSE balance

END balance

FROM (

SELECT *, LAG(balance, 1, balance) OVER (PARTITION BY client_id ORDER BY balance_month) prev_balance

FROM tablename

) t

ORDER BY client_id, balance_month DESC;