题解 | 最长连续登录天数

SELECT t3.user_id,MAX(t3.c_consec_days) max_consec_days
FROM (
    SELECT t2.user_id,t2.d_sub,COUNT(t2.d_sub) c_consec_days
    FROM(
        SELECT t1.user_id,t1.fdate,DATE_SUB(fdate,INTERVAL t1.d_ranking DAY) d_sub
        FROM (
            SELECT 
            user_id,
            fdate,
            ROW_NUMBER() OVER(partition by user_id order by fdate) d_ranking
            FROM tb_dau
        ) t1
    ) t2
    GROUP BY t2.user_id,t2.d_sub
) t3
GROUP BY t3.user_id