how to find number of active users for say 1 day,2 days, 3 days.....postgreSQL

Question

A distribution of # days active within a week: I am trying to find how many members are active for 1 day, 2days, 3days,…7days during a specific week 3/1-3/7.

Is there any way to use aggregate function on top of partition by? If not what can be used to achieve this?

select distinct memberID,count(date) over(partition by memberID) as no_of_days_active
from visitor
where date between '"2019-01-01 00:00:00"' and '"2019-01-07 00:00:00"'
order by no_of_days_active

result should look something like this

#Days Active    Count
1           20
2           32
3           678
4           34
5           3
6           678
7           2345

Answer 1

I think you want two levels of aggregation to count the number of days during the week:

select num_days_active, count(*) as num_members
from (select memberID, count(distinct date::date) as num_days_active
      from visitor
      where date >= '2019-01-01'::date and 
            date < '2019-01-08'::date
      group by memberID
     ) v
group by num_days_active
order by num_days_active;

Note that I changed the date comparisons. If you have a time component, then between does not work. And, because you included time in the constant, I added an explicit conversion to date for the count(distinct). That might not be necessary, if date is really a date with no time component.

Answer 2

Piggybacking off of @Gordon's answer, I personally like using a with statement for the subqueries:

with dat as (
    select distinct
        memberID,
        count(date) over(partition by memberID) as no_of_days_active
    from visitor
    where 1=1
        and date between '2019-01-01'::date and '2019-01-07'::date
    order by no_of_days_active
)

select 
    no_of_days_active, 
    count(no_of_days_active) no_of_days_active_cnt
from dat
group by no_of_days_active
order by no_of_days_active