how to find the maximum number of times the customer id logged in consecutively

Question

table

customer id	login
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

I want to find what is the maximum number of times the customer id logged in consecutively.

My approach is

1. Use self join to create lag (I use self join because directly using lead and lag will not work here).

2. Now somehow I need to create a column which checks if previous date is null then 0, else count 1,2,3,..... If previous date is again 0 then start counting from zero again.

customer id	login	previous date	consecutive_day_flag
1	2016-03-01	null	0
1	2016-03-02	2016-03-01	1
1	2016-03-03	2016-03-02	2
1	2016-03-05	null	0
1	2016-03-06	2016-03-05	1
1	2016-03-07	2016-03-06	2
1	2016-03-08	2016-03-08	3

-- Now I can find the max consecutive_day_flag and name the column as highest consecutive days.

highest_consecutive_day
3

---

How can I handle the step 2 of creating a consecutive_day_flag column?

Or please suggest any better approach.

I have tried above approach.

Answer 1

This is a gaps and islands problem. One method for solving it uses LEAD() to get next row, then use ROW_NUMBER() and SUM() to generate groups :

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

Result :

customer_id highest_consecutive_day
1           4 -- from 2016-03-05 to 2016-03-08 is 4 not 3

Demo here