SQL query to find user by it’s specific latest action

I have following db scheme below where all user actions are collected:

user_action
===============================

user_id action_id timestamp
===============================
1       1         2022/05/07 17:23
1       2         2022/05/07 17:24
1       1         2022/05/07 17:25
2       1         2022/05/07 17:23
2       2         2022/05/07 17:24
3       2         2022/05/07 17:23
3       1         2022/05/07 17:24


action
===============================

id    name
===============================
1     blocked
2     unblocked

The goal is to find all recently blocked users. So the expected result is to find 1 and 3 user ids since those users were recently blocked.

I’ve tried to play with following SQL below, but still do not have good understending how to finalize this:

select user_id, action_id, max(timestamp) as timestamp 
from user_action 
where action_id in (1,2) 
group by user_id, action_id

Currently query is able to return only following:

===============================
user_id action_id timestamp
1       2         2022/05/07 17:24
1       1         2022/05/07 17:25
2       1         2022/05/07 17:23
2       2         2022/05/07 17:24
3       2         2022/05/07 17:23
3       1         2022/05/07 17:24

For the result above I need to all users where action_id = 1 and timestamp is bigger than in action_id = 2

Answer

One solution is to use ROW_NUMBER inside an embedded query and then filter the result for the last timestamp and the desired action_id.

SELECT ua.user_id, ua.action_id, ua.timestamp
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) rn
      FROM user_action) ua
WHERE ua.rn = 1 AND ua.action_id = 1