count continuously postgresql data

Question

i need help with counting some data

this what i want

| user_id | action_id | count       |
------------------------------------- 
| 1       |    1      | 1           |
| 2       |    2      | 1           |
| 3       |    2      | 2           |
| 4       |    3      | 1           |
| 5       |    3      | 2           |
| 6       |    3      | 3           |
| 7       |    4      | 1           |
| 8       |    5      | 1           |
| 9       |    5      | 2           |
| 10      |    6      | 1           |

this is what i have

| user_id | action_id | count |
------------------------------- 
| 1       |    1      | 1     |
| 2       |    2      | 1     |
| 3       |    2      | 1     |
| 4       |    3      | 1     |
| 5       |    3      | 1     |
| 6       |    3      | 1     |
| 7       |    4      | 1     |
| 8       |    5      | 1     |
| 9       |    5      | 1     |
| 10      |    6      | 1     |

i really need it for create some research about second action from users how do i do it?

thank you

Answer 1

Using ROW_NUMBER should work here:

SELECT
    user_id,
    action_id,
    ROW_NUMBER() OVER (PARTITION BY action_id ORDER BY user_id) count
FROM yourTable
ORDER BY
    user_id;

Demo