MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `user` (
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `solved` int
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;

insert into user values
('2010-11-26', 2),
('2010-11-27', 4),
('2010-11-28', 6),
('2010-11-29', 10),

('2010-12-05', 11),
('2010-12-06', 11),
('2010-12-07', 12),
('2010-12-08', 12),
('2010-12-09', 12),
('2010-12-10', 12);


select * from user 


date
solved


2010-11-26 00:00:00
2

2010-11-27 00:00:00
4

2010-11-28 00:00:00
6

2010-11-29 00:00:00
10

2010-12-05 00:00:00
11

2010-12-06 00:00:00
11

2010-12-07 00:00:00
12

2010-12-08 00:00:00
12

2010-12-09 00:00:00
12

2010-12-10 00:00:00
12


WITH 

consecutiveDateGroups AS (
  SELECT
   ROW_NUMBER() OVER (ORDER BY date) AS rowNum,
   DATE_ADD(date, INTERVAL (-ROW_NUMBER() OVER (ORDER BY date)) DAY) AS consecutiveDateGroup,
   date,
   solved
  FROM user
)

SELECT *
FROM consecutiveDateGroups
ORDER BY rowNum


rowNum
consecutiveDateGroup
date
solved


1
2010-11-25 00:00:00
2010-11-26 00:00:00
2

2
2010-11-25 00:00:00
2010-11-27 00:00:00
4

3
2010-11-25 00:00:00
2010-11-28 00:00:00
6

4
2010-11-25 00:00:00
2010-11-29 00:00:00
10

5
2010-11-30 00:00:00
2010-12-05 00:00:00
11

6
2010-11-30 00:00:00
2010-12-06 00:00:00
11

7
2010-11-30 00:00:00
2010-12-07 00:00:00
12

8
2010-11-30 00:00:00
2010-12-08 00:00:00
12

9
2010-11-30 00:00:00
2010-12-09 00:00:00
12

10
2010-11-30 00:00:00
2010-12-10 00:00:00
12


WITH 
            
consecutiveDateGroups AS (
  SELECT
   ROW_NUMBER() OVER (ORDER BY date) AS rowNum,
   DATE_ADD(date, INTERVAL (-ROW_NUMBER() OVER (ORDER BY date)) DAY) AS consecutiveDateGroup,
   date
  FROM user
)
            
SELECT
  COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate
FROM consecutiveDateGroups
GROUP BY consecutiveDateGroup



consecutiveDates
minDate
maxDate


4
2010-11-26 00:00:00
2010-11-29 00:00:00

6
2010-12-05 00:00:00
2010-12-10 00:00:00

date	solved
2010-11-26 00:00:00	2
2010-11-27 00:00:00	4
2010-11-28 00:00:00	6
2010-11-29 00:00:00	10
2010-12-05 00:00:00	11
2010-12-06 00:00:00	11
2010-12-07 00:00:00	12
2010-12-08 00:00:00	12
2010-12-09 00:00:00	12
2010-12-10 00:00:00	12

rowNum	consecutiveDateGroup	date	solved
1	2010-11-25 00:00:00	2010-11-26 00:00:00	2
2	2010-11-25 00:00:00	2010-11-27 00:00:00	4
3	2010-11-25 00:00:00	2010-11-28 00:00:00	6
4	2010-11-25 00:00:00	2010-11-29 00:00:00	10
5	2010-11-30 00:00:00	2010-12-05 00:00:00	11
6	2010-11-30 00:00:00	2010-12-06 00:00:00	11
7	2010-11-30 00:00:00	2010-12-07 00:00:00	12
8	2010-11-30 00:00:00	2010-12-08 00:00:00	12
9	2010-11-30 00:00:00	2010-12-09 00:00:00	12
10	2010-11-30 00:00:00	2010-12-10 00:00:00	12