博客

用个排序的窗口函数即可实现：

select distinct user_id
from (
    select user_id
        , continue_date
        , count(login_date) as continue_day_cnt
    from (
        select user_id
            , login_date
            , login_date - row_number() over(partition by user_id order by login_date) as continue_date
        from (
            select user_id
                , substr(login_time, 1, 10) as login_date
            from dw.dw_user_login_log
            where 1 = 1
            group by user_id
                , substr(login_time, 1, 10)
        ) t
    ) m
    where 1 = 1
    group by user_id
        , continue_date
    having count(login_date) >= 3  -- 连续3天登录，根据业务场景调整这一数值
) x ;