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

select
    (
        case
            when maxday = 1 then "未连续登录"
            when maxday in (2, 3) then "连续登录2~3天"
            when maxday in (4, 5, 6, 7) then "连续登录4~7天"
            else "连续登录大于7天"
        end
    ) as days_range,
    count(1) as user_num
from
    (
        select
            uid,
            max(days) as maxday
        from
            (
                select
                    uid,
                    count(1) as days
                from
                    (
                        select
                            uid,
                            date_sub(login_date, interval rk1 day) as times
                        from
                            (
                                select
                                    uid,
                                    login_date,
                                    row_number() over (
                                        partition by
                                            uid
                                        order by
                                            login_date
                                    ) as rk1
                                from
                                    (select distinct uid,login_date from user_login_tb) t
                            ) t1
                    ) t2
                group by
                    uid,
                    times
            ) t3
        group by
            uid
    ) t4
group by
    days_range
order by user_num desc