I have a table that looks like this and it shows the number of total distinct users by day of week in a selected time frame (i.e. Apr-May)
Distinct users is calculated as:
Day of week is calculated as:
I wanted to add a column in the table to also include the average number of distinct users by day of week. For example, from selected time frame Apr 1 to June 1, there are 8 Mondays and the total number of distinct users for Mondays are 1800, then the average distinct users should be 1800/8. But I have some issue in calculating the number of Mondays. I tried count(Day of week) but it doesn’t seem to be correct.
To get number of Mondays, you can use distinct count of timestamp, since you will be reporting the same at day of week level.
However, if you intend to find average distinct user, it may not be a simple calculation of 1800/8 if users are repeated over weeks. Not sure of your use case, but please take into consideration repeated users over the week.
Sample use case: Monday1 has 1600 unique users and Monday2 has 1300 unique users, however unique users over both Mondays is 1900.
For calculating average unique user, computation should look like (1600 +1300)/2 ~ 1450
But using the aforementioned logic, it would compute as 1900/2 ~ 950.
However the computation is purely based on your use case.
