SQL面试题-统计连续n天登陆的用户(开窗函数，date

统计连续 5 天登录的用户，login 登录表：

hive> select * from temp;
OK
2020-09-01	a
2020-09-01	b
2020-09-02	a
2020-09-03	a
2020-09-03	b
2020-09-04	a
2020-09-04	b
2020-09-05	a
2020-09-06	a
2020-09-07	b
2020-09-08	a
2020-09-10	a
2020-09-11	a
2020-09-11	b
2020-09-23	b
2020-09-24	b
2020-09-26	b
2020-09-27	b
2020-09-29	b
2020-09-30	b
2020-09-12	b
2020-09-17	b
Time taken: 0.085 seconds, Fetched: 22 row(s)
hive> select distinct a.userid from (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) a join (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) b on a.userid = b.userid and a.rank = b.rank -5 and a.dt = date_add(b.dt,-5);
OK
a
Time taken: 10.525 seconds, Fetched: 1 row(s)

select distinct a.userid from (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) a join (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) b on a.userid = b.userid and a.rank = b.rank -5 and a.dt = date_add(b.dt,-5);