Count the first record in PostgreSQL-CodePudding

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

Results here