题解 | 最长连续登录天数

select user_id
,max(date_cnt) max_consec_days
from(select t1.user_id
    ,count(tem_date) date_cnt
    from(select user_id
        ,fdate
        ,date_sub(fdate,interval row_number()over(partition by user_id order by fdate)day) as tem_date
        from tb_dau 
        where fdate between '2023-01-01' and '2023-01-31'
        ) t1
    group by t1.user_id,tem_date) t2
group by user_id
# 聚合函数不能直接嵌套 max(count()) 是不符合语法的，需要用子查询
# select中有其他字段+聚合函数时一定要和groupby一起用
# 子查询的表一定要命名