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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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

-- step2:用row_number()计数
select *,
       row_number() over (PARTITION by user_id order by dt) as xrank
from (select distinct
        user_id,
        date(visit_time) as dt
    from cm.tb_user_logs) a;

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

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