题解 | 最长连续登录天数

利用ROW_NUMBER 对每个user_id登录时间进行升序排序，得到date_order列 ；
利用DATE_SUB 将fdate与date_order相减，得到“伪日期列-date2，若连续登录，date2应相同 （保证数据唯一，同一个uid 同一天登陆时间 数据不唯一时算count数会变多 ）；
COUNT计算连续登陆时长 取 MAX。（总结：用户 和 登陆时间 数据去重，根据用户id分组时间排序，若连续登陆登陆时间减去排序数相同，根据uid和相减后的日期分组计算连续登陆时间）

# select user_id,max(r) max_consec_days
# from 
# (
#     select user_id,count(r) r
#     from (
#         select distinct *,
#         fdate-row_number() over (partition by user_id order by fdate) r
#         from tb_dau
#     ) t1
#     group by user_id,r
# ) t2
# group by user_id
-----------------------------------------------------------------------------------------------

SELECT DISTINCT user_id, MAX(consec_day) AS max_consec_days
FROM(
    SELECT user_id, date2,
    COUNT(date2) AS consec_day
    FROM(
        SELECT *,
        DATE_SUB(fdate, INTERVAL date_order day) AS date2
        FROM(
            SELECT DISTINCT *,
            ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS date_order
            FROM tb_dau
            WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
            ) AS t
        ) AS t2
    GROUP BY user_id, date2
    ) AS t3
GROUP BY user_id;