I want to keep track of users logged in every day between two dates.
Let’s say I have a table my_table like this:
| user_id | login_datetime |
|---|---|
| 1 | 2021-10-02 10:00:00 |
| 1 | 2021-10-02 12:00:00 |
| 2 | 2021-10-02 12:20:00 |
| 1 | 2021-10-03 17:00:00 |
| 1 | 2021-10-04 22:00:00 |
| 2 | 2021-10-04 23:00:00 |
and given date range is from ‘2021-10-02’ to ‘2021-10-04’.
I want to get user_id = 1 in this case, because user_id = 2 is not logged in at ‘2021-10-03’
result
| user_id | login_date |
|---|---|
| 1 | 2021-10-02 |
| 1 | 2021-10-03 |
| 1 | 2021-10-04 |
Is there any solution for this?
>Solution :
One approach uses aggregation:
SELECT user_id
FROM my_table
WHERE login_datetime >= '2021-10-02' AND login_datetime < '2021-10-05'
GROUP BY user_id
HAVING COUNT(DISTINCT DATE(login_datetime)) = 3; -- range has 3 dates in it
Demo
The HAVING clause asserts that any matching user must have 3 distinct dates present, which would imply that such a user would have login activity on all dates from 2021-10-02 to 2021-10-04 inclusive.