How to make revenue $0 for months prior to decrease in SQL?

I have the following data:

client_id	balance_month	balance
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

What is the most efficient way to make all prior months prior to the most recent month with a decrease in balance have $0 such that:

client_id	balance_month	balance
100000000	September 1,2021 12:00 AM	$400
100000000	August 1,2021 12:00 AM	$100
100000000	July 1,2021 12:00 AM	$0
100000000	June 1,2021 12:00 AM	$0
200000000	September 1,2021 12:00 AM	$99
200000000	August 1,2021 12:00 AM	$0
200000000	July 1,2021 12:00 AM	$0
200000000	June 1,2021 12:00 AM	$0

Answer

You can use LAG() window function to check if there is a decrease in the balance and MAX() window function to check when the last decrease happenned:

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;

See the demo.