I have a table like this (after i user filter)
ID user_id action_type created_date
1 123 3 27/1/2022
2 123 3 30/1/2022
3 123 3 08/03/2022
4 123 3 08/03/2022
then i have this query
select date_trunc('week', created_date::timestamptz) as "Week"
,count(distinct user_id) filter (where action_type = 3) as "linkbank"
From action_logs
Where action_type = 3 and user_id = '123'
Group by "Week"
And the result of query belike this
Week linkbank
24/01/2022 1
07/03/2022 1
So if i have to count the first time the client linkbank, what i should change in my query?
For example if i change the query with user_id =123
, i want the result will take the first row only
I will try everything you comment.
Tks.
CodePudding user response:
If you want it for only one user_id
select date_trunc('week', created_date::timestamptz) as "Week"
,count(distinct user_id) filter (where action_type = 3) as "linkbank"
From action_logs
Where action_type = 3 and user_id = 123
Group by "Week"
order by "Week"
limit 1;
If you want it for all users
with l as (
select row_number() over (partition by user_id order by user_id, date_trunc('week', created_date::timestamptz)) as rn,user_id, date_trunc('week', created_date::timestamptz) as "Week"
,count(distinct user_id) filter (where action_type = 3) as "linkbank"
From action_logs
Where action_type = 3
Group by user_id,"Week"
)
select * from l where rn = 1