how to find consecutive day for login

Question

there is table in my database (MySQL 5.7.36),I try to find consecutive day with condition

if consecutive day > 7

consecutive day will be set zero

DATE_SERV
2022-01-01
2022-01-02
2022-01-03
2022-01-05
2022-01-06
2022-01-09
2022-01-10
2022-01-11

my actually expect table is

DATE_SERV	day_consecutive
2022-01-01	1
2022-01-02	2
2022-01-03	3
2022-01-05	1
2022-01-06	2
2022-01-09	1
2022-01-10	2
2022-01-11	3
2022-01-12	4
2022-01-13	5
2022-01-14	6
2022-01-15	7
2022-01-16	1
2022-01-17	2

Answer 1

I wrote this up before, thinking you were using MySQL 8.x (which supports window functions, unfortunately 5.x does not). Anyway, just posting it in case it's useful to someone else ...

---

You can adapt the approach from this blog Gaps and Islands Across Date Ranges. First identify the "islands" or groups of consecutive dates

SELECT
        DATE_SERV
        , SUM( IF( DATEDIFF(DATE_SERV, Prev_Date) = 1, 0, 1) ) OVER(
                   ORDER BY DATE_SERV 
        )  AS DateGroup_Num
FROM
      (
         SELECT DATE_SERV
                , LAG(DATE_SERV,1) OVER (
                       ORDER BY DATE_SERV
                ) AS Prev_Date
         FROM   YourTable
      ) grp

Which produces this result:

DATE_SERV	DateGroup_Num
2022-01-01	1
2022-01-02	1
2022-01-03	1
2022-01-05	2
2022-01-06	2
2022-01-09	3
2022-01-10	3
2022-01-11	3

Then use a conditional SUM(...) to find the earliest date per group, and display the number of consecutive days since that date:

SELECT 
       t.DATE_SERV
       , DATEDIFF( 
            t.DATE_SERV
            , MIN(t.DATE_SERV) OVER( 
                  PARTITION BY t.DateGroup_Num 
                  ORDER BY t.DATE_SERV 
             )
        ) +1 AS Consecutive_Days
FROM ( 
        SELECT
               DATE_SERV
              , SUM( IF( DATEDIFF(DATE_SERV, Prev_Date) = 1, 0, 1) ) OVER(
                    ORDER BY DATE_SERV 
                )  AS DateGroup_Num
        FROM
        (
                SELECT DATE_SERV
                       , LAG(DATE_SERV,1) OVER (
                            ORDER BY DATE_SERV
                         ) AS Prev_Date
                FROM   YourTable
        ) grp
    ) t

Results:

DATE_SERV	Consecutive_Days
2022-01-01	1
2022-01-02	2
2022-01-03	3
2022-01-05	1
2022-01-06	2
2022-01-09	1
2022-01-10	2
2022-01-11	3

db<>fiddle here