determines the number of daily active users per month

Question

I have a sample data:

ID  time    user_id event
1   2021-11-11  15  access benifits
2   2021-12-25  6   submit policy
3   2022-01-02  9   submit policy
4   2021-12-01  13  create payslip
5   2021-11-24  15  create payslip
6   2021-11-25  15  create payslip
7   2022-01-03  7   create payslip
8   2021-12-22  9   access benifits
9   2021-11-08  2   submit policy
10  2022-01-15  5   submit policy
11  2021-11-17  9   access benifits
12  2022-01-12  11  create payslip
13  2021-12-27  1   submit policy
14  2021-11-10  4   submit policy
15  2022-01-24  2   create payslip

I want determines the number of daily active users per month.

I tried it Like

SELECT  user_id, MONTHNAME(time) mon , COUNT(*) cnt FROM ACTIVITIES 
GROUP by user_id, MONTH(time) HAVING COUNT(*) = 1

This gives me accurate result for each user and each month.

result of my query.

user_id mon cnt
1   December    1
2   January 1
2   November    1
4   November    1
5   January 1
6   December    1
7   January 1
9   January 1
9   November    1
9   December    1
11  January 1
13  December    1

I want to count distinct user_id from this query result.

I am using MySQL.

Answer 1

You just need to put your query (without the HAVING, I'm not sure what the point of that was) in a subquery and total it:

select mon, count(*) active_users
from (
    SELECT  user_id, MONTHNAME(time) mon , COUNT(*) cnt FROM ACTIVITIES 
    GROUP by user_id, MONTH(time)
)
group by mon