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
Advertisement
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