SQL Server 2022

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

create table mytable (
  customer_id int,	
  login date
);

insert into mytable values
(1,	'2016-03-01'),
(1,	'2016-03-02'),
(1,	'2016-03-03'),
(1,	'2016-03-05'),
(1,	'2016-03-06'),
(1,	'2016-03-07'),
(1,	'2016-03-08');
7 rows affected

WITH cte AS (
  SELECT *,
        LEAD(login, 1, login) OVER (PARTITION BY customer_id ORDER BY login) AS LEAD,
        CASE WHEN DATEDIFF(DAY, login, LEAD(login, 1, login) OVER (PARTITION BY customer_id ORDER BY login)) <= 1 THEN 1 ELSE 0 END AS day_diff
  FROM mytable
)
SELECT *
FROM cte


customer_id
login
LEAD
day_diff


1
2016-03-01
2016-03-02
1

1
2016-03-02
2016-03-03
1

1
2016-03-03
2016-03-05
0

1
2016-03-05
2016-03-06
1

1
2016-03-06
2016-03-07
1

1
2016-03-07
2016-03-08
1

1
2016-03-08
2016-03-08
1


WITH cte AS (
  SELECT *,
        CASE WHEN DATEDIFF(DAY, login, LEAD(login, 1, login) OVER (PARTITION BY customer_id ORDER BY login)) <= 1 THEN 1 ELSE 0 END AS day_diff
  FROM mytable
),
cte2 as (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login) 
       - SUM(day_diff) OVER (PARTITION BY customer_id, day_diff ORDER BY login) AS grp
  FROM cte
),
cte3 AS (
  SELECT customer_id, grp, COUNT(1) AS consecutive_day
  FROM cte2
  GROUP BY customer_id, grp
)
SELECT customer_id, MAX(consecutive_day) AS highest_consecutive_day
FROM cte3
GROUP BY customer_id


customer_id
highest_consecutive_day


1
4

customer_id	login	LEAD	day_diff
1	2016-03-01	2016-03-02	1
1	2016-03-02	2016-03-03	1
1	2016-03-03	2016-03-05	0
1	2016-03-05	2016-03-06	1
1	2016-03-06	2016-03-07	1
1	2016-03-07	2016-03-08	1
1	2016-03-08	2016-03-08	1