【2025大数据秋招】SQL练习篇

SQL164 2021年11月每天新用户的次日留存率

题目：统计2021年11月每天新用户的次日留存率（保留2位小数）

id	uid	artical_id	in_time	out_time	sign_cin
1	102	9001	2021-11-01 10:00:00	2021-11-01 10:00:09	0
1	103	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0

注：次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序

思路：
1、我们首先需要获取新增用户表和用户活跃表两张表
2、用户活跃表的获取思路是我们将in_time时间和out_time时间进行union操作。

select
	uid,
	date(in_time) dt
from tb_user_log
union
select
	uid,
	date(out_time)
from tb_user_log

这段代码我们可以获取到以下类似的效果：

uid	in_time	out_time
1	2023-11-24 09:00:00	2023-11-24 09:30:00
1	2023-11-24 13:45:00	2023-11-24 14:15:00
2	2023-11-24 10:30:00	2023-11-24 11:00:00
3	2023-11-25 08:00:00	2023-11-25 08:30:00
3	2023-11-25 11:15:00	2023-11-25 11:45:00

在上述查询语句之后，我们会通过Union做一个去重的操作，可以得到：

uid	dt
1	2023-11-24
2	2023-11-24
3	2023-11-25

由于题目当中计算留存率中包括了out_time时间超过第二天也算是两天活跃，所以我们对out_time进行一个union的操作，最后得到上述的展示结果。

用户新增表

接下来我们需要获取用户新增表，那么我们需要获取到用户第一次登录的时间，我们需要使用函数Min（）来获取用户第一次登录的时间

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

联结表

最后一步是我们需要将用户新增表和用户活跃表进行join操作，因为我们需要计算的是每天的用户留存率，所以在group by字段当中我们选择时间字段。接着，针对求解次日留存率的情况，也就是意味着t1表当中的时间应该是等于t2表中的时间的前一天，因为当天用户注册了之后，第二天的时候活跃了，这时候才能计入用户留存率里边。

select
    t1.dt,
    round(count(t2.uid) / count(t1.uid), 2) uv_rate
from(
        select
            uid,
            min(date(in_time)) dt
        from tb_user_log
        group by uid
) as t1
left join (

select
    uid,
    date(in_time) dt
from tb_user_log
union
select
    uid,
    date(out_time) dt
from tb_user_log) as t2
on t1.uid = t2.uid
and t1.dt = date_sub(t2.dt, INTERVAL 1 DAY)
where date_format(t1.dt,'%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt;