SQL经典面试题--连续登录问题

1,求连续登录了k天的人数

mt_t1表（user_id用户名，load_date登录日期）
步骤：

1）先筛掉同一用户在同一天登录多次的情况
select user_id, load_date from mt_t1 group by user_id,load_date

2)分组row_number排序（例：用户1，0801排名1，0802排名2，0804排名3）
row_number() over(partition by user_id order by load_date) as rk

3)用日期减去排名天数（用户1：0731，0731，0801）as new_date
date_sub(load_date,interval rk day) as new_date

4)按用户与new_date分组后，计数
select count(1) group by user_id,new_date

5)筛选出计数 >= k
group by usr_id,new_date
having count(1) >= k

select count(distinct user_id) from
(select user_id,new_date,count(1)
from
(select *,date_sub(load_date,interval rnk day) as new_date

(select *,row_number()over(partition by user_id order by load_date) as rnk
from
(select user_id,load_date from mt_t1 group by user_id,load_date ) a
) b
) c
group by user_id,new_date
having count(1) >=2
) d

SQL184

最大连续天数
SQL194？？？？