MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE habit_progress_logs (
    id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    habit_id bigint unsigned NULL,
    `type` enum('repetitions', 'minutes', 'fail', 'skip'),
    `value` bigint NOT NULL,
    created_at TIMESTAMP NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO habit_progress_logs (id, habit_id, type, value, created_at) VALUES
  (1, 4, 'repetitions', 1, '2023-07-15 13:49:07'),
  (2, 4, 'repetitions', 1, '2023-07-14 13:49:07'),
  (3, 4, 'repetitions', 7, '2023-07-16 12:49:07'),
  (4, 4, 'repetitions', 2, '2023-07-17 12:49:07'),
  (5, 4, 'repetitions', 2, '2023-07-19 12:49:07'),
  (6, 4, 'repetitions', 2, '2023-08-20 12:49:07'),
  (7, 4, 'repetitions', 2, '2023-08-21 12:49:07'),
  (8, 4, 'repetitions', 2, '2023-08-23 12:49:07'),
  (9, 4, 'repetitions', 2, '2023-08-24 12:49:07'),
  (10, 4, 'repetitions', 2, '2023-08-25 12:49:07'),
  (11, 4, 'repetitions', 2, '2023-08-26 12:49:07'),
  (12, 4, 'repetitions', 2, '2023-08-27 12:49:07'),
  (13, 4, 'repetitions', 2, '2023-08-28 12:49:07'),
  (14, 4, 'repetitions', 2, '2023-09-13 12:49:07'),
  (15, 4, 'repetitions', 2, '2023-09-14 12:49:07'),
  (16, 4, 'repetitions', 2, '2023-09-15 12:49:07'),
  (17, 4, 'repetitions', 2, '2023-09-15 14:03:07'),
  (18, 4, 'repetitions', 2, '2023-09-16 15:03:07'),
  (19, 4, 'repetitions', 2, '2023-09-17 15:03:07');

Records: 19  Duplicates: 0  Warnings: 0


WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) AS rnk,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
)
SELECT * FROM streaks;


habit_id
dt
rnk
grp


4
2023-07-14
1
2023-07-13

4
2023-07-15
2
2023-07-13

4
2023-07-16
3
2023-07-13

4
2023-07-17
4
2023-07-13

4
2023-07-19
5
2023-07-14

4
2023-08-20
6
2023-08-14

4
2023-08-21
7
2023-08-14

4
2023-08-23
8
2023-08-15

4
2023-08-24
9
2023-08-15

4
2023-08-25
10
2023-08-15

4
2023-08-26
11
2023-08-15

4
2023-08-27
12
2023-08-15

4
2023-08-28
13
2023-08-15

4
2023-09-13
14
2023-08-30

4
2023-09-14
15
2023-08-30

4
2023-09-15
16
2023-08-30

4
2023-09-15
16
2023-08-30

4
2023-09-16
17
2023-08-30

4
2023-09-17
18
2023-08-30


WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
),
streaks2 AS (
    SELECT
        habit_id,
        grp,
        COUNT(DISTINCT dt) AS length,
        ROW_NUMBER() OVER (PARTITION BY habit_id ORDER BY grp DESC) AS rn
    FROM streaks
    GROUP BY habit_id, grp
)
SELECT *
FROM streaks2;


habit_id
grp
length
rn


4
2023-08-30
5
1

4
2023-08-15
6
2

4
2023-08-14
2
3

4
2023-07-14
1
4

4
2023-07-13
4
5


WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
),
streaks2 AS (
    SELECT
        habit_id,
        grp,
        COUNT(DISTINCT dt) AS length,
        ROW_NUMBER() OVER (PARTITION BY habit_id ORDER BY grp DESC) AS rn
    FROM streaks
    GROUP BY habit_id, grp
)
SELECT habit_id, length AS currentStreak
FROM streaks2
WHERE rn = 1;


habit_id
currentStreak


4
5


SELECT
  habit_id,
  dt,
  @grp := IF(@prevHabitId = habit_id AND dt = @prevDate + INTERVAL 1 DAY, @grp, @grp + 1) AS grp,
  @prevDate := dt,
  @prevHabitId := habit_id
FROM (
  SELECT habit_id, DATE(created_at) AS dt
  FROM habit_progress_logs
  JOIN (SELECT @prevHabitId := NULL, @prevDate := NULL, @streak := 1, @grp := 0) i
  WHERE habit_id = 4
  GROUP BY habit_id, dt
  ORDER BY habit_id, dt
) s;


habit_id
dt
grp
@prevDate := dt
@prevHabitId := habit_id


4
2023-07-14
1
2023-07-14
4

4
2023-07-15
1
2023-07-15
4

4
2023-07-16
1
2023-07-16
4

4
2023-07-17
1
2023-07-17
4

4
2023-07-19
2
2023-07-19
4

4
2023-08-20
3
2023-08-20
4

4
2023-08-21
3
2023-08-21
4

4
2023-08-23
4
2023-08-23
4

4
2023-08-24
4
2023-08-24
4

4
2023-08-25
4
2023-08-25
4

4
2023-08-26
4
2023-08-26
4

4
2023-08-27
4
2023-08-27
4

4
2023-08-28
4
2023-08-28
4

4
2023-09-13
5
2023-09-13
4

4
2023-09-14
5
2023-09-14
4

4
2023-09-15
5
2023-09-15
4

4
2023-09-16
5
2023-09-16
4

4
2023-09-17
5
2023-09-17
4


SELECT habit_id, COUNT(*) AS currentStreak
FROM (
  SELECT
    habit_id,
    dt,
    @grp := IF(@prevHabitId = habit_id AND dt = @prevDate + INTERVAL 1 DAY, @grp, @grp + 1) AS grp,
    @prevDate := dt,
    @prevHabitId := habit_id
  FROM (
    SELECT habit_id, DATE(created_at) AS dt
    FROM habit_progress_logs
    JOIN (SELECT @prevHabitId := NULL, @prevDate := NULL, @grp := 0) i
    WHERE habit_id = 4
    GROUP BY habit_id, dt
    ORDER BY habit_id, dt
  ) s
) t1
GROUP BY habit_id, grp
ORDER BY habit_id, grp DESC
LIMIT 1


habit_id
currentStreak


4
5


/* A slightly different approach with user defined variables */
SELECT habit_id, dt - INTERVAL rn DAY AS seq_grp, COUNT(*) AS currentStreak
FROM (
  SELECT *, @rn := @rn + 1 AS rn
  FROM (
    SELECT habit_id, DATE(created_at) AS dt
    FROM habit_progress_logs
    JOIN (SELECT @rn := 0) init
    GROUP BY habit_id, dt
    ORDER BY habit_id, dt
  ) t1
) t2
GROUP BY habit_id, seq_grp
ORDER BY seq_grp DESC
LIMIT 1;


habit_id
seq_grp
currentStreak


4
2023-08-30
5
habit_id	dt	rnk	grp
4	2023-07-14	1	2023-07-13
4	2023-07-15	2	2023-07-13
4	2023-07-16	3	2023-07-13
4	2023-07-17	4	2023-07-13
4	2023-07-19	5	2023-07-14
4	2023-08-20	6	2023-08-14
4	2023-08-21	7	2023-08-14
4	2023-08-23	8	2023-08-15
4	2023-08-24	9	2023-08-15
4	2023-08-25	10	2023-08-15
4	2023-08-26	11	2023-08-15
4	2023-08-27	12	2023-08-15
4	2023-08-28	13	2023-08-15
4	2023-09-13	14	2023-08-30
4	2023-09-14	15	2023-08-30
4	2023-09-15	16	2023-08-30
4	2023-09-15	16	2023-08-30
4	2023-09-16	17	2023-08-30
4	2023-09-17	18	2023-08-30