Streaks of consecutive days
Find runs of consecutive values with the day-minus-row-number trick. About 12 minutes.
Consecutive means a constant gap
Days 4, 5, 6 are consecutive. Number them 1, 2, 3 in order. Each day goes up by one and each number goes up by one, so day minus number never changes: 3, 3, 3.
When there is a gap, say the next day is 9 and gets number 4, the difference jumps to 5. Each streak gets its own constant value. That value is just a label for the streak.
SELECT day,
day - ROW_NUMBER() OVER (
ORDER BY day
) AS grp
FROM days;| day |
|---|
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 9 |
Days 1, 2, 3 get row numbers 1, 2, 3. The difference is 0 every time: one streak.
Group by the label
Once every row carries its streak label, finding streaks is ordinary grouping. GROUP BY the label, then COUNT(*) is the streak's length and MIN and MAX of the day are where it starts and ends.
Two traps. Duplicate days break the numbering, so take DISTINCT days first. And a window function can't be grouped in the same SELECT, so compute the label in a subquery or a CTE.
WITH g AS (
SELECT day,
day - ROW_NUMBER() OVER (
ORDER BY day
) AS grp
FROM days
)
SELECT MIN(day), MAX(day), COUNT(*)
FROM g
GROUP BY grp;Days 4, 5, 6, 9 are numbered 1, 2, 3, 4. What are the values of day minus row number?
- A3, 3, 3, 5
- B4, 5, 6, 9
- C1, 1, 1, 1
Show the answer
3, 3, 3, 5. 4-1, 5-2, 6-3 are all 3. 9-4 is 5, so the gap shows up as a new label.
Streaks of three
The logins table has one row per login, with the user and the day number. A user can log in more than once on the same day. Show the user_id of every user who logged in on at least 3 consecutive days, each user once, in order of user_id.
logins(id, user_id, day)
Up to 2,000 logins