题解 | #最长连续登录天数#

select 
t1.user_id,t1.max_consec_days
from
(select
    t.user_id,count(t.day_diff) as max_consec_days,row_number() over(partition by t.user_id order by count(t.day_diff) desc ) as rank_day
from
    (
        SELECT
            user_id,
            fdate,
            TIMESTAMPDIFF (
                day,
                MIN(fdate) OVER (
                    PARTITION BY
                        user_id
                ),
                fdate
            ) + 1 AS timediff,
            row_number() over (
                partition by
                    user_id
                order by
                    fdate
            ) as rank_day,
            (
                TIMESTAMPDIFF (
                    day,
                    MIN(fdate) OVER (
                        PARTITION BY
                            user_id
                    ),
                    fdate
                ) + 1
            ) - (
                row_number() over (
                    partition by
                        user_id
                    order by
                        fdate
                )
            ) as day_diff
        FROM
            tb_dau
        order by
            user_id
    ) t
group by
    t.user_id,
    t.day_diff
order by
    t.user_id,count(t.day_diff) desc) t1
where t1.rank_day = 1

所有的连续问题都可以使用该思路，先计算出timestampdiff - row_number的辅助列day_diff，该列反映了天数是否连续，如果值相同就是连续，所以按照day_diff进行分组count计数得到count_day_diff，就能得到连续登录天数的描述性统计，本题需要统计每个用户连续登录天数最久的天数，对用户分组+day_diff的分组进行row_number的排序，取row_number = 1即得到答案

或者对用户分组后取max(count_day_diff)即可