I want to answer the following question using the below Mysql tables.

How many days were the user active on avg. (had an action) in the last week?
I want to Display user Avg by day, where ((user action is not 0)/unique day) in the last 7 days.

0 means the user is not active and 1 means active.

I am still a newbie in SQL and here is what I have tried so far. I am not really sure of my answers.

SELECT u.username, COUNT(u.snapshot_date) as 'active_days', a.action
FROM actions a
JOIN  users u
    ON a.user_id = u.user_id
WHERE  NOT (a.action = 0)
GROUP BY u.username;

my output

username active_days action
Albert        2        1
Paul          4        1
Ronaldo       2        1
Messi         1        1

users table

user_id username    snapshot_date
    1   Albert       2022-01-10
    2   Paul         2022-01-10
    3   Blessing     2022-01-10
    4   Ronaldo      2022-01-22
    5   Messi        2022-01-01

action table

action_id action snapshot_date user_id

1            0    2022-01-10    1
2            1    2022-01-10    2
3            0    2022-01-10    3
4            1    2022-01-22    4
5            1    2022-01-01    5
6            0    2022-01-10    2
7            0    2022-01-10    1
8            0    2022-01-10    3
9            0    2022-01-22    2
10           0    2022-01-01    4
11           0    2022-01-10    2
12           1    2022-01-10    1
13           0    2022-01-10    3
14           1    2022-01-22    2
15           1    2022-01-01    4
16           1    2022-01-10    2
17           1    2022-01-10    2
18           0    2022-01-10    1
19           1    2022-01-22    1
20           0    2022-01-01    5

CodePudding user response：

Average of last week

Since there are 7 days, you can divide the count by 7

SELECT u.username, COUNT(u.snapshot_date) / 7 as active_days, 1 as action
FROM actions a
JOIN  users u
    ON a.user_id = u.user_id
WHERE  NOT (a.action = 0)
GROUP BY u.username, u.snapshot_date;

For the second problem you can average the sum of action:

SELECT u.username, SUM(a.action) / 7 as action_days, 1 as action
FROM actions a
JOIN  users u
    ON a.user_id = u.user_id
WHERE  NOT (a.action = 0)
GROUP BY u.username, u.snapshot_date;