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 |