I have a table like that
id | amount | balance
1 | 10 | 0
3 | 20 | 0
7 | 25 | 0
20 | 20 | 0
I need to calculate the balance field so the result is this:
id | amount | balance
1 | 10 | 10
3 | 20 | 30
7 | 25 | 55
20 | 20 | 75
I can't figure out how to calculate balance = prev_row.balance + amount. I though of using CTE but I need to point to the last row of CTE in a way something like WHERE cte.id < t.id ORDER BY cte.id DESC LIMIT 1 but apparently it doesn't work.
Any ideas how it can be achieved? (subqueries, temp tables etc)
