题解 | #统计最大连续登录天数区间#
统计最大连续登录天数区间
https://www.nowcoder.com/practice/a6b5dc2f033c4d8eb3c481e25dd74481
with cte as ( select distinct uid,login_date,dense_rank()over(order by login_date) as dk from user_login_tb ), cte1 as ( select uid,adddate(login_date,-dk) as num from cte ), cte2 as ( select uid,case when max(con_days)=1 then '未连续登录' when max(con_days)<=3 then '连续登录2~3天' when max(con_days)<=7 then '连续登录4~7天' else '连续登录大于7天' end as days_range from ( select uid,num,count(*) as con_days from cte1 group by uid,num ) a group by uid ) select days_range,count(*) as user_num from cte2 group by days_range order by user_num desc