how to get current streak and longest streak of all users in mysql

Question

I am facing one issue for fetching the current streak of all users and longest streak of all users. I have below table user_bible_trackings

id    user_id    date_read
1        1       2021-08-21
2        1       2021-08-22
3        1       2021-08-23
4        1       2021-08-26
5        1       2021-08-27 // current_streak 2 and longest streak is 3
6        3       2021-08-21
7        3       2021-08-24
8        3       2021-08-25
9        3       2021-08-26 
10        3      2021-08-26 // current_streak 3 and longest streak is 3

My expected output is below :-

user_id   current_streak   longest_streak
 1             2                 3
 3             3                 3

i am able to fetch current streak data for particular user but i want data of all users. i have tried below:-

SELECT *
            FROM (
               SELECT t.*, IF(@prev + INTERVAL 1 DAY = t.d, @c := @c + 1, @c := 1) AS streak, @prev := t.d as streak_date
               FROM (
                   SELECT date_read AS d, COUNT(*) AS n
                   FROM user_bible_trackings
                   where user_id = 1
                   group by date_read
               ) AS t
               INNER JOIN (SELECT @prev := NULL, @c := 1) AS vars
            ) AS t
            ORDER BY streak_date DESC LIMIT 1

@Akina Thank you for your query i have updated my question. Now see above my user_bible_trackings table. For user_id 3 reads bible 26Aug 2021 two times So current streak must be 3 and longest streak too. Would Appericiate if you can help me in this

@Akina test for this rows
INSERT INTO `test` (`id`, `user_id`, `date_read`) VALUES
   (1, 1, '2021-08-21'),
   (2, 1, '2021-08-22'),
   (3, 1, '2021-08-23'),
   (4, 1, '2021-08-26'),
  (5, 1, '2021-08-27'),
  (6, 3, '2021-08-21'),
  (7, 3, '2021-08-24'),
  (8, 3, '2021-08-25'),
  (9, 3, '2021-08-26'),
  (11, 3, '2021-08-26'),
  (12, 3, '2021-08-26'),
  (13, 3, '2021-08-26')

My expceted Output for above

user_id   current_streak   longest_streak
 1             2                 3
 3             3                 3

But your query returns below output

user_id   current_streak   longest_streak
 1             2                 3
 3             4                 4

Please help me how to resolve this issue.

Answer 1

Not optimal, not compact, but clear solution:

WITH 
cte1 AS (
SELECT *,
       CASE date_read 
           WHEN LAG(date_read) OVER (PARTITION BY user_id ORDER BY date_read) + INTERVAL 1 DAY
           THEN 0
           ELSE 1 END group_start
FROM test
),
cte2 AS (
SELECT *,
       SUM(group_start) OVER (PARTITION BY user_id ORDER BY date_read) group_num
FROM cte1
),
cte3 AS (
SELECT user_id, group_num,
       COUNT(*) group_count
FROM cte2
GROUP BY user_id, group_num
)
SELECT DISTINCT 
       user_id,
       FIRST_VALUE(group_count) OVER (PARTITION BY user_id ORDER BY group_num DESC) current_streak,
       MAX(group_count) OVER (PARTITION BY user_id) longest_streak
FROM cte3

detailed fiddle