[SQL] Fetch last row for each month, or previous month if not found

I have a table balance_history that has the following structure and data (simplified)

Account Balance Transaction_date
a 100 29/09/2024
a 75 20/08/2024
a 65 18/08/2024
b 50 15/08/2024
c 200 10/07/2024

The balance of an account appears on that table only when there is a transaction. I can get the current/up-to-date balance of each account by running rank over partition statement for the entire table.

However, what I need is to show the monthly balance of each account. That means, if there was no transaction for a particular month, I want the query to search from the previous months and find the last transaction which has the latest balance (there is id column).

I want to show this outcome:

Month Account Balance
07/2024 a 0
07/2024 b 0
07/2024 c 200
08/2024 a 75
08/2024 b 50
08/2024 c 200
09/2024 a 100
09/2024 b 50
09/2024 c 200

As you see, even though there was no transaction for account c in September, I want to still show the balance. How can I can do this?

Continue reading...