题解 | #2021年11月每天新用户的次日留存率#

【解题思路】

若是今天注册的用户明天也活跃，那么用活跃的时间减去1必有一天的日期与注册日期是同一天。

【第一步】获得有新用户注册的日期。

select
    uid
    ,min(in_time) as dt
from tb_user_log
group by uid; 

+-----+---------------------+
| uid | dt                  |
+-----+---------------------+
| 101 | 2021-11-01 10:00:00 |
| 102 | 2021-11-01 10:00:00 |
| 103 | 2021-11-01 10:00:01 |
| 104 | 2021-11-02 10:00:28 |
| 105 | 2021-11-03 11:00:53 |
+-----+---------------------+
5 rows in set (0.02 sec)

【第二步】获得用户活跃的所有日期

----------错误代码-----------------
(select 
    uid
    ,in_time as dt
from tb_user_log)
union 
(select
    uid 
    ,out_time as dt
from tb_user_log);

+-----+---------------------+
| uid | in_time             |
+-----+---------------------+
| 101 | 2021-11-01 10:00:00 |
| 102 | 2021-11-01 10:00:00 |
| 103 | 2021-11-01 10:00:01 |
| 101 | 2021-11-02 10:00:09 |
| 103 | 2021-11-02 10:00:51 |
| 104 | 2021-11-02 10:00:28 |
| 101 | 2021-11-03 11:00:55 |
| 104 | 2021-11-03 11:00:45 |
| 105 | 2021-11-03 11:00:53 |
| 101 | 2021-11-04 11:00:55 |
| 101 | 2021-11-01 10:00:42 |
| 102 | 2021-11-01 10:00:09 |
| 103 | 2021-11-01 10:01:50 |
| 101 | 2021-11-02 10:00:28 |
| 103 | 2021-11-02 10:00:59 |
| 104 | 2021-11-02 10:00:50 |
| 101 | 2021-11-03 11:01:24 |
| 104 | 2021-11-03 11:00:55 |
| 105 | 2021-11-03 11:00:59 |
| 101 | 2021-11-04 11:00:59 |
+-----+---------------------+
20 rows in set (0.03 sec)
--------------------在这里就将数据整理一下格式，否则后面因为时间的原因会出现错误------------------------------------
(select 
    uid
    ,date(in_time) as dt
from tb_user_log)
union 
(select
    uid 
    ,date(out_time) as dt
from tb_user_log);
+-----+------------+
| uid | dt         |
+-----+------------+
| 101 | 2021-11-01 |
| 102 | 2021-11-01 |
| 103 | 2021-11-01 |
| 101 | 2021-11-02 |
| 103 | 2021-11-02 |
| 104 | 2021-11-02 |
| 101 | 2021-11-03 |
| 104 | 2021-11-03 |
| 105 | 2021-11-03 |
| 101 | 2021-11-04 |
+-----+------------+
10 rows in set (0.03 sec)

【第三步】对活跃日期减去1，得到起始日期。

--------错误代码--------
select
    uid
    ,date_sub(dt,interval 1 day) as day_init 
from    
(
(select 
    uid
    ,in_time as dt
from tb_user_log)
union 
(select
    uid 
    ,out_time as dt
from tb_user_log)
) t2   
;
+-----+---------------------+
| uid | day_init            |
+-----+---------------------+
| 101 | 2021-10-31 10:00:00 |
| 102 | 2021-10-31 10:00:00 |
| 103 | 2021-10-31 10:00:01 |
| 101 | 2021-11-01 10:00:09 |
| 103 | 2021-11-01 10:00:51 |
| 104 | 2021-11-01 10:00:28 |
| 101 | 2021-11-02 11:00:55 |
| 104 | 2021-11-02 11:00:45 |
| 105 | 2021-11-02 11:00:53 |
| 101 | 2021-11-03 11:00:55 |
| 101 | 2021-10-31 10:00:42 |
| 102 | 2021-10-31 10:00:09 |
| 103 | 2021-10-31 10:01:50 |
| 101 | 2021-11-01 10:00:28 |
| 103 | 2021-11-01 10:00:59 |
| 104 | 2021-11-01 10:00:50 |
| 101 | 2021-11-02 11:01:24 |
| 104 | 2021-11-02 11:00:55 |
| 105 | 2021-11-02 11:00:59 |
| 101 | 2021-11-03 11:00:59 |
+-----+---------------------+
20 rows in set (0.03 sec)


-----------------------
select
    uid
    ,date_sub(dt,interval 1 day) as day_init 
from    
(
(select 
    uid
    ,date(in_time) as dt
from tb_user_log)
union 
(select
    uid 
    ,date(out_time) as dt
from tb_user_log)
) t2   
;
+-----+------------+
| uid | day_init   |
+-----+------------+
| 101 | 2021-10-31 |
| 102 | 2021-10-31 |
| 103 | 2021-10-31 |
| 101 | 2021-11-01 |
| 103 | 2021-11-01 |
| 104 | 2021-11-01 |
| 101 | 2021-11-02 |
| 104 | 2021-11-02 |
| 105 | 2021-11-02 |
| 101 | 2021-11-03 |
+-----+------------+
10 rows in set (0.00 sec)

【第四步】与每日新增用户表连接统计结果即可。

select
    date(a.dt)
    ,round(ifnull(count(b.day_init)/count(a.uid),0),2) as uv_rate
from    
    (select
        uid
        ,date(min(in_time)) as dt
    from tb_user_log
    group by uid) a
left join 
    (select
        uid
        ,date_sub(dt,interval 1 day) as day_init 
    from    
    ( (select 
            uid
            ,date(in_time) as dt
        from tb_user_log)
        union 
        (select
            uid 
            ,date(out_time) as dt
        from tb_user_log
        )
    ) t2
    ) b
on a.uid = b.uid and a.dt = b.day_init
where date_format(a.dt,'%Y-%m')='2021-11' 
group by date(a.dt)
order by date(a.dt);