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
CodePudding user response：
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
CodePudding user response：
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
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' ;
CodePudding user response：
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
CodePudding user response：
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
CodePudding user response：
You can do this :
- you have to make id column in action table as primary key
- then you also have to create the action_id column in the user_action table as a foreign key from the id column in the action table
To get the data you want, you can follow the following query:
select user_action.*
from user_action, action
where user_action.action_id = '1'
and user_action.user_id in (1,3)
group by user_id
order by user_action.timestamp asc;
Then a table like the following will appear:
| user_id | action_id | timestamp |
|---|---|---|
| 1 | 1 | 2022-05-07 17:23:00 |
| 3 | 1 | 2022-05-07 17:24:00 |