I have a table of users of varying ID's. The database contains a recording for each user and whether the user has logged in or not at on a specific date. The table contains recordings for every day. I want to write an SQL query that returns all the instances where a user logs in for 3 or more consecutive days, alongside the user's id, the number of total consecutive days, and the start and end date for the log in streak. (Of course a user could have more than one streak)
The table would look something like this:
| id | logged_in | date |
|---|---|---|
| 1022 | Y | 7/11/2022 |
| 1022 | Y | 7/12/2022 |
| 1022 | N | 7/13/2022 |
| 1022 | Y | 7/14/2022 |
| 1022 | Y | 7/15/2022 |
| 1022 | Y | 7/16/2022 |
| ... | ... | ... |
| 2519 | Y | 8/20/2022 |
| 2519 | Y | 8/21/2022 |
| 2519 | Y | 8/22/2022 |
| 2519 | Y | 8/23/2022 |
| 2519 | Y | 8/24/2022 |
| 2519 | N | 8/25/2022 |
And the result:
| id | count | start date | end date |
|---|---|---|---|
| 1022 | 3 | 7/14/2022 | 7/16/2022 |
| 2519 | 5 | 8/20/2022 | 8/24/2022 |
Thank you.
CodePudding user response：
This is a gaps and islands problem. One approach uses the difference in row numbers method:
WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) rn1,
ROW_NUMBER() OVER (PARTITION BY id, logged_in ORDER BY date) rn2
FROM yourTable
)
SELECT id, COUNT(*) AS count, MIN(date) AS start_date, MAX(date) AS end_date
FROM cte
WHERE logged_in = 'Y'
ORDER BY id, rn1 - rn2;
CodePudding user response：
This is my solution
SELECT ID,COUNT(looged_in),MIN(date),MAX(date) FROM TABLE WEHRE looged_in = "Y"
GROUP BY ID
Hope the solution helps you