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

Question

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 1

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

Answer 2

You can use CROSS APPLY to retrieve the last action_id by timestamp and then apply a filter.

SELECT DISTINCT ua.user_id, ca.action_id, ca.timestamp
FROM user_action ua
CROSS APPLY (SELECT TOP 1 *
             FROM user_action
             WHERE user_id = ua.user_id
             ORDER BY timestamp DESC) ca
WHERE ca.action_id = 1

Answer 3

An easier solution for most dbms might be:

with cte as (
                select *,
                       row_number() over(partition by user_id order by timestamp_dt desc) as row_num
                from user_action
                 ) 
select user_id,
       action_id,
       timestamp_dt
from cte
where row_num=1
and action_id=1 ;

Result:

user_id   action_id   timestamp_dt
  1           1       2022-05-07 17:25:00
  3           1       2022-05-07 17:24:00

Demo

Note that you don't need action table because you have action_id=1. If you should include action table than use:

with cte as (
             select *,
                    row_number() over(partition by user_id order by timestamp_dt desc) as row_num
              from user_action
              ) 
select user_id,
       action_id,
       timestamp_dt
from cte
inner join action a on a.id=cte.action_id
where row_num=1
and a.name='blocked' ;

Demo

Answer 4

You can use an inner join with a subquery to retrieve what you are looking for

SELECT u.user_id,u.action_id,u.timestamp 
FROM user_action u
INNER JOIN ( 
    SELECT ua.user_id, MAX(ua.timestamp) as ts FROM user_action ua
    GROUP BY ua.user_id
)as t ON u.user_id = t.user_id AND u.timestamp = t.ts
WHERE action_id = 1;

This should work for the common dbms and return this according to your data

user_id   action_id   timestamp
=========================================
  1           1       2022-05-07 17:25:00
  3           1       2022-05-07 17:24:00

You can check the fiddle right here

Note: You can avoid having the action table as they mentioned