Daily count of user_ids who have visited my store 4 or more than 4 times every day-CodePudding

I have a table of user_id who have visited my platform. I want to get count of only those user IDs who have visited my store 4 or more times for each user and for every day for a duration of 10 days. To achieve this I am using this query:

select date(arrival_timestamp), count(user_id)
from mytable
where date(arrival_timestamp) >= current_date-10
and date(arrival_timestamp) < current_date
group by 1
having count(user_id)>=4
order by 2 desc
limit 10;

But this query is virtually taking all the users having count value greater than 4 and not on a daily basis which covers almost every user and hence I am not able to segregate only those users who vist my store more than once on a particular day. Any help in this regard is appreciated.

Thanks

CodePudding user response：

you can try this

with list as (
select user_id, count(*) as user_count, array_agg(arrival_timestamp) as arrival_timestamp
from mytable
where date(arrival_timestamp) >= current_date-10
and date(arrival_timestamp) < current_date
group by user_id)
select user_id, unnest(arrival_timestamp)
from list
where user_count >= 4

CodePudding user response：

From a list of daily users that have visited your store 4 or more times a day over the last 10 days (the internal query) select these who have 10 occurencies, i.e. every day.

select user_id
from
(
 select user_id 
 from the_table 
 where arrival_timestamp::date between current_date - 10 and current_date - 1
 group by user_id, arrival_timestamp::date
 having count(*) >= 4
) t
group by user_id
having count(*) = 10;