牛客网 SQL大厂面试真题篇 SQL11 每天的日活数及新用户占比

描述

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	101	9001	2021-10-31 10:00:00	2021-10-31 10:00:09	0
2	102	9001	2021-10-31 10:00:00	2021-10-31 10:00:09	0
3	101	0	2021-11-01 10:00:00	2021-11-01 10:00:42	1
4	102	9001	2021-11-01 10:00:00	2021-11-01 10:00:09	0
5	108	9001	2021-11-01 10:00:01	2021-11-01 10:00:50	0
6	108	9001	2021-11-02 10:00:01	2021-11-02 10:00:50	0
7	104	9001	2021-11-02 10:00:28	2021-11-02 10:00:50	0
8	106	9001	2021-11-02 10:00:28	2021-11-02 10:00:50	0
9	108	9001	2021-11-03 10:00:01	2021-11-03 10:00:50	0
10	109	9002	2021-11-03 11:00:55	2021-11-03 11:00:59	0
11	104	9003	2021-11-03 11:00:45	2021-11-03 11:00:55	0
12	105	9003	2021-11-03 11:00:53	2021-11-03 11:00:59	0
13	106	9003	2021-11-03 11:00:45	2021-11-03 11:00:55	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计每天的日活数及新用户占比

注：

• 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
• 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
• 新用户占比保留2位小数，结果按日期升序排序。

输出示例：

示例数据的输出结果如下

dt	dau	uv_new_ratio
2021-10-30	2	1.00
2021-11-01	3	0.33
2021-11-02	3	0.67
2021-11-03	5	0.40

解释：

2021年10月31日有2个用户活跃，都为新用户，新用户占比1.00；

2021年11月1日有3个用户活跃，其中1个新用户，新用户占比0.33；

题解：

总体逻辑如下：

# 1.计算用户第一次登录的时间
# 2.根据1计算每天的新用户数
# 3.计算每日活跃用户数
# 4.把每日活跃用户数和每天的新用户数做连接，连接条件是日期
# 5.计算新用户数/活跃用户数,因为连接的两个表里面都已经按照日期分组了，所以连接后的表不需要再分组了

 第一步：先统计每个用户的最小登录日期，再统计每个日期的用户数，即为每天的新用户数

select dt,count(1) new_user
from(
      -- 用户第一次登录时间
        select uid,min(date(in_time)) dt
        from tb_user_log
        group by uid
)t
group by dt

第二步：每日活跃用户数

select dt,count(distinct uid) dau
from(
        select uid,date(in_time) dt from tb_user_log 
        union all
        select uid,date(out_time) dt from tb_user_log
)t
group by dt

 

第三步：将两个表作右连接（因为有可能某天没有新用户数，因此要右连接），再计算新用户占比

select t2.dt,dau,ifnull(round(new_user/dau,2),0) uv_new_ratio
from(
    -- 每天的新用户数
    select dt,count(1) new_user
    from(
        -- 用户第一次登录时间
        select uid,min(date(in_time)) dt
        from tb_user_log
        group by uid)t
    group by dt
)t1
-- 因为有可能某天没有新用户数，因此要右连接
right join(
    -- 每日活跃用户数
    select dt,count(distinct uid) dau
    from(
        select uid,date(in_time) dt from tb_user_log  -- 先在里面date()
        union all
        select uid,date(out_time) dt from tb_user_log
    )t
    group by dt
)t2
on t1.dt=t2.dt