查询连续登陆3天的用户id和登陆天数

1
-- 查询连续登陆3天的用户id和登陆天数
2
-- step1:用户登录日期去重
3
select distinct
4
    user_id,
5
    date(visit_time) as dt
6
from cm.tb_user_logs;
7

8
-- step2:用row_number()计数
9
select *,
10
       row_number() over (PARTITION by user_id order by dt) as xrank
11
from (select distinct
12
        user_id,
13
        date(visit_time) as dt
14
    from cm.tb_user_logs) a;
15

16
-- step3: 日期减去计数值得到差值delta
17
select *, date_sub(dt, INTERVAL xrank DAY) as delta
18
from (select *,
19
             row_number() over (PARTITION by user_id order by dt) as xrank
20
      from (select distinct
21
                user_id,
22
                date(visit_time) as dt
23
            from cm.tb_user_logs) a
24
      ) b;
25

26
-- step4:根据id和结果分组并计算总和,大于等于3的即为连续登录3天的用户
27
select user_id, min(dt) as start_date, count(*) as days
28
from (select *, date_sub(dt, INTERVAL xrank DAY) as delta
29
      from (select *,
30
                 row_number() over (PARTITION by user_id order by dt) as xrank
31
            from (select distinct
32
                      user_id,
33
                        date(visit_time) as dt
34
                  from cm.tb_user_logs) a
35
            ) b
36
      ) c
37
GROUP BY user_id, delta
38
having count(*) >= 3;