题解 | 最长连续登录天数

with continue_three_days as(
    select today.user_id,
           today.fdate as today_date,
           case when yesterday.fdate is null then today.fdate else null end as begin_date,
           case when tomorrow.fdate is null then today.fdate else null end as end_date
    from tb_dau as today
    left join tb_dau as yesterday
    on yesterday.fdate = date_sub(today.fdate, interval 1 day) 
       and yesterday.user_id = today.user_id
    left join tb_dau as tomorrow
    on tomorrow.fdate = date_add(today.fdate, interval 1 day) 
       and tomorrow.user_id = today.user_id
    where today.fdate between '2023-1-1' and '2023-1-31'
)
select b.user_id,max(datediff(e.end_date,b.begin_date) + 1) as max_consec_days
from(
    select user_id,begin_date,row_number() over(PARTITION BY user_id order by begin_date asc) as begin_order
    from continue_three_days
    where begin_date is not null
)as b
inner join(
    select user_id,end_date,row_number() over(PARTITION BY user_id order by end_date asc) as end_order
    from continue_three_days
    where end_date is not null
)as e
on b.user_id = e.user_id and b.begin_order = e.end_order
group by user_id
order by user_id asc

上面的代码，只有"PARTITION BY user_id"是ai修改后加上的，其他的全部是自己写的！想了半个多小时，敲敲改改又调了快一个小时，第一次运行的时候就跑通了（自测运行）！虽然第一次提交的时候只有一个用例通过了（只差一个PARTITION BY user_id），但是100%自己写的代码可以运行，还是很有成就感（而且也只差一点点 想了很久实现出来的大逻辑没有问题）。

看了官方解答，感觉在没见过类似思路的情况下，不是我的智商能想出来的...所以虽然自己的方法很土很笨，但是能跑对也很可以了！那就简单梳理一下自己的思路吧：

1.首先是如何构建起一个“判断连续登录”的逻辑体系。我能想到的就是看当前日期的前一天and后一天，前面接不上，那这个日期肯定是某段的开始，后面接不上则是某段的结束，因此做了一个双重self join（三表连接），取id和当前日期记为today，再创建两列，分别代表某段的begin和end，是哪种的话就在对应列填入日期，不是的话就null。定义出来的cte(命名为continue_three_days)长这样：

2.怎么计算连续登录天数？显然是用datediff( , ) + 1，把每一段的开始结尾都放进去作差，算天数的话记得＋1。问题是怎么得到这样的数据？一个显然的事情是，上面表中的begin_date和end_date的数据条数和其中各自的null值个数都一样，也就是非空的数据数量都一样，也就是左边第k个非空的begin_date一定和右边第k个非空的end_date是同一段连续登录！所以我在主查询里面用了两个嵌套的子查询并且join起来：从排除掉null值后，用row_number（）记录各自是第几个非空的date，然后把这个row_number和id作为连接的键值，长这样：

3.max(datediff(e.end_date,b.begin_date) + 1)，group by user_id一下就解决了。

官答懒得梳理了，没事多看看吧，确实很难自己想到。