1454 活跃用户连续5天连续登录（没写出来） 日期减去排序号归为一类

Create table If Not Exists Accounts (id int, name varchar(10))
Create table If Not Exists Logins (id int, login_date date)
Truncate table Accounts
insert into Accounts (id, name) values ('1', 'Winston')
insert into Accounts (id, name) values ('7', 'Jonathan')
Truncate table Logins
insert into Logins (id, login_date) values ('7', '2020-05-30')
insert into Logins (id, login_date) values ('1', '2020-05-30')
insert into Logins (id, login_date) values ('7', '2020-05-31')
insert into Logins (id, login_date) values ('7', '2020-06-01')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-03')
insert into Logins (id, login_date) values ('1', '2020-06-07')
insert into Logins (id, login_date) values ('7', '2020-06-10')

SELECT distinct  e.id,d.name
FROM
(SELECT id
FROM
    #日期减去行数，连续的化为一组
    (SELECT id,login_date,date_sub(login_date,INTERVAL b.rank DAY) AS 'r'

    FROM
        #对不重复的登录的日期进行排序
        (SELECT id,login_date,row_number() over(partition by id order by login_date) as 'rank'
        FROM
            #去重，因为有人一天中重复登录
            (select distinct id,login_date
            from Logins )a 
        )b 
    )c
group by id,r
having count(r) >=5 )e
left join  Accounts  d on  e.id = d.id

主要的点在于date_sub 函数：

SELECT id,login_date,row_number() over(partition by id order by login_date) as 'rank'
FROM
(select distinct id,login_date
from Logins )a