题解 | #统计最大连续登录天数区间#新手简单思路，容易理解

with tiaojian as (
select 
uid,
login_date,
row_number()over(partition by uid order by login_date asc) as m 
from user_login_tb
group by uid,login_date
),tiaojian2 as (
select 
uid,
max(diff) as pdiff
from(
select 
uid,
login_date,
subdate(login_date,m) as pm,
count(uid)over(partition by uid,subdate(login_date,m) ) as diff
from tiaojian 
) as t 
group by uid
)


select 
t.days_range,
count(distinct uid) as   user_num
from(
select 
case when pdiff between 2 and 3 then "连续登录2~3天"
     when pdiff between 4 and 7 then "连续登录4~7天"
     when pdiff<2 then "未连续登录" 
     else "连续登录大于7天" end as days_range
,uid
from tiaojian2
) as t 
group by t.days_range
order by user_num desc,t.days_range desc