I am trying to create a query for my website to get the total number of active users for a selected time period and for a specific user account ID to filter for teams.
My current query works but without a specific user account ID so this is for the total users and for my weekly time period (active users the past week including today):
WITH RECURSIVE Dates AS (
SELECT CURDATE() - INTERVAL 6 DAY AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM Dates
WHERE date < CURDATE()
)
SELECT
CASE
WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY)
WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY)
WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00")
ELSE
date
END AS id,
COALESCE(COUNT(activity_user), 0) AS users
FROM Dates
LEFT JOIN activity ON date = activity_date
LEFT JOIN users ON activity_user = users_id
GROUP BY id
ORDER BY id ASC;
This is the result:
+------------+-------+
| id | users |
+------------+-------+
| 2024-10-02 | 25|
| 2024-10-03 | 64|
| 2024-10-04 | 10|
| 2024-10-05 | 22|
| 2024-10-06 | 27|
| 2024-10-07 | 16|
| 2024-10-08 | 3|
+------------+-------+
The issue is that if I try to do:
LEFT JOIN users ON activity_user = users_id WHERE users_account = 10 for my second left join, to filter for the account with ID of 10, I get an empty set instead of showing me the dates but with 0 users, for each date. The expected result should be:
+------------+-------+
| id | users |
+------------+-------+
| 2024-10-02 | 0|
| 2024-10-03 | 0|
| 2024-10-04 | 0|
| 2024-10-05 | 0|
| 2024-10-06 | 0|
| 2024-10-07 | 0|
| 2024-10-08 | 0|
+------------+-------+
And if I do:
LEFT JOIN users ON activity_user = users_id AND users_account = 10, it gets disregarded completely and doesn't work and I get my original result of:
+------------+-------+
| id | users |
+------------+-------+
| 2024-10-02 | 25|
| 2024-10-03 | 64|
| 2024-10-04 | 10|
| 2024-10-05 | 22|
| 2024-10-06 | 27|
| 2024-10-07 | 16|
| 2024-10-08 | 3|
+------------+-------+
I have tried using every type of SQL JOIN for these tables but none of them seem to work. I simply want to filter by the user_account field and to show 0 on dates that have no active users. It works for the total amount of users but not when I want to filter for a specific team (user_account).
The below code is for where you got an empty result but expected all zeros. I think that meant user_account 10 did not log on those days.
Can you try,
WITH RECURSIVE Dates AS (SELECT CURDATE() - INTERVAL 6 DAY AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM Dates
WHERE date < CURDATE())
SELECT
CASE
WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY)
WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY)
WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00")
ELSE
date
END AS id,
if(users_account is null, "0", COALESCE(COUNT(activity_user), 0) ) AS users
FROM Dates
LEFT JOIN activity ON date = activity_date
left JOIN users ON activity_user = users_id
where users_account = 10
GROUP BY id
ORDER BY id ASC;