MySQL 8.0

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

CREATE TABLE tablename (Client INTEGER, User INTEGER, Date DATE, Flag BOOLEAN);
INSERT INTO tablename VALUES
  (1, 1, '2022-11-15', 1), (1, 1, '2022-11-22', 0), (1, 1, '2022-11-29', 1), (1, 1, '2022-12-06', 1),
  (1, 2, '2022-11-15', 1), (1, 2, '2022-11-22', 1), (1, 2, '2022-11-29', 1), (1, 2, '2022-12-06', 1),
  (1, 2, '2022-12-13', 0), (1, 2, '2022-12-20', 0), (1, 2, '2022-12-27', 0), (1, 2, '2023-01-03', 0),
  (1, 2, '2023-01-10', 1), (1, 2, '2023-01-17', 1), (1, 2, '2023-01-24', 1),
  (1, 3, '2022-11-08', 1), (1, 3, '2022-11-15', 0), (1, 3, '2022-11-22', 0);

Records: 18  Duplicates: 0  Warnings: 0


WITH cte AS (
  SELECT Client, User,
         GROUP_CONCAT(CASE WHEN Flag THEN Date END ORDER BY Date) AS dates,
         CHAR_LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(Flag ORDER BY Date SEPARATOR ''), '0', '-1')) AS consecutive
  FROM tablename
  GROUP BY Client, User
)
SELECT Client, User,
       NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(dates, ',', -consecutive), ',', 1), '') AS first_date,
       CASE WHEN consecutive > 0 THEN SUBSTRING_INDEX(dates, ',', -1) END AS last_date,
       consecutive 
FROM cte;


Client
User
first_date
last_date
consecutive


1
1
2022-11-29
2022-12-06
2

1
2
2023-01-10
2023-01-24
3

1
3
null
null
0


WITH 
  cte1 AS (SELECT *, SUM(NOT Flag) OVER (PARTITION BY Client, User ORDER BY Date) AS grp FROM tablename),
  cte2 AS (SELECT *, MAX(grp) OVER (PARTITION BY Client, User) AS max_grp FROM cte1)
SELECT Client, User,
       MIN(CASE WHEN Flag THEN Date END) AS first_date,
       MAX(CASE WHEN Flag THEN Date END) AS last_date,
       SUM(Flag) AS consecutive
FROM cte2 
WHERE grp = max_grp
GROUP BY Client, User;


Client
User
first_date
last_date
consecutive


1
1
2022-11-29
2022-12-06
2

1
2
2023-01-10
2023-01-24
3

1
3
null
null
0

Client	User	first_date	last_date	consecutive
1	1	2022-11-29	2022-12-06	2
1	2	2023-01-10	2023-01-24	3
1	3	null	null	0