题解 | #统计最大连续登录天数区间#

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