连续登陆问题

题目：有一张用户签到表【temp】，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段：日期【log_date】，用户id【uid】，用户当天是否签到【sign_in：0否1是】

问题1：请计算截至当前每个用户已经连续签到的天数

思路：计算最近一次未签到的日期，再用当前日期减去那个日期

select 
    uid,
    datediff('2020-10-01',day_max) as day_cnt
from(
    select 
        uid,
        max(log_date) as day_max
    from temp
    where sign_in = 0
    group by uid
)t 

问题2：请计算每个用户历史以来最大的连续签到天数

select 
    uid,
    max(log_days) as max_days
from(
    select 
        uid,
        rn,
        count(*) as log_days
    from(
        select 
            uid,
            log_date,
            date_sub(log_date,row_number() over(partition by uid order by log_date)) as rn
        from temp 
        where sign_in = 1
    )a
    group by uid,rn
    )b
group by uid

问题3：请计算至少连续签到3天的用户

select 
    uid
from(
    select 
        uid,
        lead(log_date,2) over(partition by uid order by log_date) as last_date
    from temp
    where sign_in = 1
)t
where datediff(last_date,log_date) = 2