How to calculate the remaining amount per row?

I have a wallet table like this:

// wallet
+----+----------+--------+
| id | user_id  | amount |
+----+----------+--------+
| 1  | 5        | 1000   |
| 2  | 5        | -200   |
| 3  | 5        | -100   |
| 4  | 5        | 500    |
+----+----------+--------+

I want to make a view that calculates the remaining amount per row. Something like this:

+----+----------+--------+------------------+
| id | user_id  | amount | remaining_amount |
+----+----------+--------+------------------+
| 1  | 5        | 1000   | 1000             |
| 2  | 5        | -200   | 800              |
| 3  | 5        | -100   | 700              |
| 4  | 5        | 500    | 1200             |
+----+----------+--------+------------------+

Any idea how can I do that?

>Solution :

MySQL 8 has window function for that purpose, like SUM() OVER

for your sample data, this will calculate the running SUM for every user_id

vital for th function to work is the PARTITION BY and the ORDER BY to get the right amount

SELECT
`id`, `user_id`, `amount`
, SUM(`amount`) OVER(PARTITION BY `user_id` ORDER BY `id`) run_sum
  FROM wallet

fiddle