SQL implements continuous login days and maximum login days

Title: calculate the number of consecutive login days of users
Problem extension: users who log in for 3 consecutive days, maximum login days, etc

The field user id and login time date given in sheet1 are shown in the following table:

1. Cleaning data

Because it is the calculation of continuous login days, it is accurate to hours, minutes and seconds, and there may be situations in the table, such as multiple continuous logins and multiple logins on the same day. Therefore, data cleaning is carried out.
Here, you only need to use date for cleaning.

SELECT id,date(date) time from sheet1 GROUP BY id ,time

Get:

2. Sort the login days of users

You need to use the window function row here_ Number () over (partition by field 1 order by field 2)

SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a

Get:

3. Subtract the sorting number from the login time (the number is the number of days)

If the user logs in continuously, for example, on January 2, 3 and 4, the order is 1, 2 and 3. January 2 minus the corresponding serial number 1 will get January 1, and January 3 minus the corresponding serial number 2 will also get January 1. Therefore, if the user logs in continuously, the login date minus the number of days as the serial number will get the same date. Count the same date after subtracting the serial number. This number is the number of consecutive login days. Date is required here_ sub(time，interval number day)

SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a

Get:

4. Counts the number of days a user logged in

Users may have logged in in different time periods, so the number of consecutive login days may occur several times. The following is the number of consecutive login days of users

SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime

Get:

Extension question 1: log in for three consecutive days

For the above code, continue nesting

SELECT DISTINCT id
from
(SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime) a
where rk=3

Get:

Or use having to filter directly, but if the user logs in for 3 consecutive days for many times, it needs to be nested again.

SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime
having rk=3

Get:

Extension question 2: maximum login days

SELECT DISTINCT id,max(rk)
from
(SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime) a
GROUP BY id

Get: