Hello I would like to know the highest count of consecutive days a user has trained for.
My logs table that stores the records looks like this:
| id | user_id | day | ground_id | created_at |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2023-01-24 10:00:00 |
| 2 | 1 | 2 | 1 | 2023-01-25 10:00:00 |
| 3 | 1 | 3 | 1 | 2023-01-26 10:00:00 |
| 4 | 1 | 4 | 1 | 2023-01-27 10:00:00 |
| 5 | 1 | 5 | 1 | 2023-01-28 10:00:00 |
The closest I could get is with this query, which does work only if the user has trained on one ground at a day.
SELECT COUNT(*) AS days_in_row
FROM (SELECT row_number() OVER (ORDER BY day) - day AS grp
FROM logs
WHERE created_at >= '2023-01-24 00:00:00'
AND user_id = 1) x
GROUP BY grp
logs table:
| id | user_id | day | ground_id | created_at |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2023-01-24 10:00:00 |
| 2 | 1 | 2 | 1 | 2023-01-25 10:00:00 |
| 3 | 1 | 3 | 1 | 2023-01-26 10:00:00 |
| 4 | 1 | 4 | 1 | 2023-01-27 10:00:00 |
| 5 | 1 | 5 | 1 | 2023-01-28 10:00:00 |
This query would return a count of 5 consecutive days which is correct.
However my query doesn't work once a user trains multiple times on different training grounds in one day:
logs table:
| id | user_id | day | ground_id | created_at |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2023-01-24 10:00:00 |
| 2 | 1 | 2 | 1 | 2023-01-25 10:00:00 |
| 3 | 1 | 3 | 1 | 2023-01-26 10:00:00 |
| 4 | 1 | 3 | 2 | 2023-01-26 10:00:00 |
| 5 | 1 | 4 | 1 | 2023-01-27 10:00:00 |
Than the query from above would return a count of 2 consecutive days which is not what I expect instead I would expect the number four because the user has trained the following days in row (1,2,3,4).
Thank you for reading.
CodePudding user response：
Select only distinct data of interest first
SELECT min(created_at) start, COUNT(*) AS days_in_row
FROM (SELECT created_at, row_number() OVER (ORDER BY day) - day AS grp
FROM (
select distinct day, created_at
from logs
where created_at >= '2023-01-24 00:00:00'
AND user_id = 1) t
) x
GROUP BY grp