题解 | 最长连续登录天数
最长连续登录天数
https://www.nowcoder.com/practice/cb8bc687046e4d32ad38de62c48ad79b
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一起用
# 子查询的表一定要命名
查看23道真题和解析