题解 | #最长连续登录天数#

/*1.创建一列：每个登录日期的上次登录日期
--2.求每个登录日期和上次登录日期的间隔天数
--3.间隔天数大于1 或 空值，标记为1；间隔天数等于1，标记为0
--4.按照用户分组、日期升序，将标记累计求和，以此得到间隔登录日期的分组/次数
--5.求每个用户、每次连续登录的天数
--6.求每个用户、连续登录的最大天数
*/
with t1 as(
select user_id,
       fdate,
       lag(fdate,1) over(partition by user_id order by fdate) as pre_fdate
from tb_dau
where fdate like '2023-01%'
),

t2 as(
select user_id,
       fdate,
       case when pre_fdate is not null then datediff(fdate,pre_fdate)
       else null end as diff 
from t1
),

t3 as(
select user_id,
       fdate,
       diff,
       case when diff=1 then 0
            when diff is null or diff>1 then 1
       end as mark
from t2
),

t4 as(
select user_id,
       fdate,
       diff,
       mark,
       sum(mark) over(partition by user_id order by fdate) as fdate_group
from t3
),

t5 as(
select user_id,count(fdate) as consec_days
from t4
group by user_id,fdate_group
)

select user_id,max(consec_days) as max_consec_days
from t5
group by user_id

求各个用户的最长连续登录天数(分组求最大值)，不是所有连续登录天数中最长的（全组最大值）。输出格式：

user_id max_cons_days

user1 3

user2 4

user3 5

...