Count the number of distinct days that users have logged in

Question & Answer
,
1

I would like to count the number of days that users have logged in over a period of time and then group the results by the number of days logged in so I can create a power user curve.
My table looks like this:
user_id | event | date
1 | login | 2022-01-01 14:30
1 | login | 2022-01-01 15:30
2 | login | 2022-01-02 09:00
1 | login | 2022-01-02 11:59
1 | login | 2022-01-03 12:34

I created the following table below but it is not what I wanted:
user_id | No. of days logged in
1 | 3
2 | 1

I would like to create a table like this instead:
No. of days | No. of Users
1 | 1
2 | 0
3 | 1
I have tried using the distinct count over function like so: distinctCountOver({extract(‘DD’, {date}), [{user_id}], PRE_AGG)

2

Hi @dhruv

May be you should have the distinctCountOver({extract(‘DD’, {date}), [{user_id}], PRE_AGG) inside ifelse to pick up the days wise aggregation and then count the user_id, like below

no. of days = ifelse(distinctCountOver({extract(‘DD’, {date}, [{user_id}]) = 1, 1, distinctCountOver({extract(‘DD’, {date}, [{user_id}]) = 2, 2, distinctCountOver({extract(‘DD’, {date}, [{user_id}]) = 3, 3, distinctCountOver({extract(‘DD’, {date}, [{user_id}]) = 4, 4, distinctCountOver({extract(‘DD’, {date}, [{user_id}]) = 5, 5, 6)

No. of Users = count(user_id)

1 Like