连续登陆超过N天的用户

一、题目

现有用户登录日志表 user_login_log,包含用户ID(user_id),登录日期(login_date)。数据已经按照用户日期去重，请查出连续登录超过4天的用户ID

样例数据

二、思路

这个是一个技巧性的题目，如果连续登录有一个特点，就是：连续登录则 登录日期 - 登录次序 的差值日期相同。

举个例子，如果001这个用户10.1、10.2、10.3、10.4连续登录。登录次序则是10.1号的次序为1，10.2号的次序为2 依次类推，这时套用前面的公式：10.1 - 1 = 09.30， 10.2-10.1-2 = 09.30，10.3-10.1-3 = 09.30，10.4-10.1-4 = 09.30，差值日期都是09月30号。

所以这个题目的思路清晰之后只需要分别计算出登录次序、以及差值日期，最后统计出现次数大于4即可。

三、解答

剖析一下解题思路：

1）需要按照user_id和登录日期去重，题目中声明已经去重则这一步可以省略

2）计算每个user_id的登录次序：rn，如10.1号记为0，10.2号记为1，可以使用row_number()函数

3）计算日期差值：date_sub(login_date, rn)

4）计算每个差值出现的次数：count

完整解答如下：

with tab_deduplication as (
       -- 去重（如果没去重需要按用户和日期去重）,并按user_id分组后标记此用户的登录升序序号
       select 
              user_id,
              login_date,
              row_number() over(partition by user_id order by login_date) rn -- 登录次序
       from (
              -- 去重  
              select 
                     user_id,
                     login_date
              from user_login_log
              group by user_id, 
                     login_date
       ) t11
)

select 
       distinct user_id
from (
       -- 每个差值出现的次数
       select 
              user_id, 
              df_flag,
              count(login_date) cnt
       from (
              -- 计算登录日期差值
              select
                     user_id,
                     login_date,
                     date_sub(login_date, rn) as df_flag -- 登录日期差值
              from tab_deduplication
       ) t11
       group by user_id, df_flag
) t21
where cnt > 4

只有用户001连续登录过5天

数据

-- 建表
create table mst.user_login_log (
  user_id string,
  login_date string
) 
stored as parquet;

insert into table mst.user_login_log values
('001', '2025-10-01'),
('001', '2025-10-02'),
('001', '2025-10-03'),
('001', '2025-10-04'),
('001', '2025-10-05'),
('002', '2025-10-02'),
('002', '2025-10-03'),
('002', '2025-10-09');