给定用户登录表，求表中每一天的3天留存率

1
-- 给定用户登录表，求表中每一天的"3天留存率"
2

3
-- step 0 表准备，用户ID，登录时间
4
create table cm.tb_user_logs(
5
    user_id int,
6
    visit_time datetime
7
);
8

9
insert into cm.tb_user_logs
10
(user_id, visit_time)
11
values
12
(1001, '2022-10-01 08:01:23'),
13
(1001, '2022-10-01 08:11:15'),
14
(1002, '2022-10-01 08:22:19'),
15
(1003, '2022-10-01 09:00:53'),
16
(1002, '2022-10-02 18:00:13'),
17
(1004, '2022-10-02 13:30:43'),
18
(1004, '2022-10-02 15:06:22'),
19
(1005, '2022-10-02 08:00:39'),
20
(1002, '2022-10-03 08:00:13'),
21
(1003, '2022-10-03 18:00:13'),
22
(1004, '2022-10-03 21:00:13'),
23
(1006, '2022-10-03 22:00:13'),
24
(1001, '2022-10-04 11:10:13'),
25
(1002, '2022-10-04 12:00:13'),
26
(1002, '2022-10-04 09:00:13'),
27
(1004, '2022-10-04 08:00:13'),
28
(1006, '2022-10-04 08:00:13'),
29
(1004, '2022-10-05 08:00:13'),
30
(1005, '2022-10-05 08:00:13'),
31
(1002, '2022-10-05 08:00:43'),
32
(1003, '2022-10-05 12:00:13'),
33
(1004, '2022-10-05 10:00:43'),
34
(1006, '2022-10-05 08:00:11'),
35
(1001, '2022-10-06 09:00:47'),
36
(1001, '2022-10-06 07:00:15'),
37
(1002, '2022-10-06 18:00:43'),
38
(1003, '2022-10-07 20:00:19'),
39
(1002, '2022-10-07 21:00:23'),
40
(1004, '2022-10-07 22:00:43');
41

42
-- step 1 创建视图，进行用户ID和时间去重
43
create view A as
44
select distinct
45
       user_id,
46
       date(visit_time) as dt
47
from cm.tb_user_logs;
48

49
-- step2. 计算每天的"3天活跃用户数"，即对于每一天而言，在当天的活跃用户中，3天后还活跃的那些用户---分子
50
select
51
    t1.dt      as dt,
52
    count(t1.user_id) as 3day_active_cnt
53
from A as t1
54
         join A t2 on t1.dt = t2.dt - 3
55
where 1 = 1
56
  and t1.user_id = t2.user_id
57
group by t1.dt;
58

59
-- step3. 计算每天的活跃用户数---分母
60
select
61
    dt,
62
    count(user_id) as active_cnt
63
from A
64
group by dt;
65

66
-- step4. 将上述两个步骤结果，按照同一天的日期，进行关联，求比率即可
67
select t3.dt,
68
       t3.3days_active_cnt,
69
       t4.active_cnt,
70
       t3.3days_active_cnt * 1.0 / t4.active_cnt as 3day_alive_ratio
71
from (select t1.dt,
72
             count(t1.user_id) as 3days_active_cnt
73
            from A as t1
74
                     join A t2 on t1.dt = t2.dt - 3
75
            where 1=1
76
                and t1.user_id = t2.user_id
77
            group by dt) as t3
78
         join
79
         (select
80
              dt,
81
              count(user_id) as active_cnt
82
          from A
83
          group by dt
84
          ) as t4
85
         on t3.dt = t4.dt
86
order by t3.dt;
87

88
select distinct (user_id)
89
from cm.tb_user_logs
90
where date(visit_time)='2022-10-01';
91

92
select distinct (user_id)
93
from cm.tb_user_logs
94
where date(visit_time)='2022-10-04';