前言
主要解决留存率问题:
题目1来源于牛客网SQL70 牛客每个人最近的登录日期(五):
牛客每天有很多人登录,请你统计一下牛客每个日期新用户的次日留存率。
问题:
思路:
求第二天的留存率,首先要求出每个用户的首次登录时间(group by min),然后看首次登录时间的下一天该用户是否登录(left join原表,if(null))
代码如下:
1)每个用户的首次登录时间
select
user_id,min(date) dt
from
login
group by
user_id
2)每个用户首次登录的第二天是否登录
select
t2.dt,round(sum(if(l.user_id is null,0,1))/count(*),3)
//注:sum(if(l.user_id is null,0,1)可以更换为avg(if(l.user_id is null,0,1)
//有点脱裤子放屁了
from
(
select
user_id,min(date) dt
from
login
group by
user_id
) t2
left join
login l
on
t2.user_id=l.user_id
and
l.date=date_add(t2.dt,interval 1 day)
group by
t2.dt
3)还没完
上述代码的输出结果为:
可以看到,上述代码只是输出了有新用户登录的日期的留存率,还缺少没有新用户登录日期的留存率
所以需要补齐
select
date dt,0.000
from
login
where
date not in
(
select
min(date)
from
login
group by
user_id
)
综上,总代码为:
(select
t2.dt,round(sum(if(l.user_id is null,0,1))/count(*),3)
from
(
select
user_id,min(date) dt
from
login
group by
user_id
) t2
left join
login l
on
t2.user_id=l.user_id
and
l.date=date_add(t2.dt,interval 1 day)
group by
t2.dt)
union
(select
date dt,0.000
from
login
where
date not in
(
select
min(date)
from
login
group by
user_id
)
)
order by dt
题目2:SQL大厂面试真题9:2021年11月每天新用户的次日留存率
解法与问题1类似,区别在于存在两个时间,in_time和out_time,在判断新登录用户首次登录日期的下一天是否登录时,需要判断这两个时间。同时,无新登录用户的日期无需输出,即不用后面的union
总代码如下:
select
a.dt,round(count(distinct b.uid)/count(a.uid),2)
from
(
select
uid,date(min(in_time)) dt
from
tb_user_log
group by
uid
) a
left join
(
select uid,date(in_time) dt from tb_user_log
union
select uid,date(out_time) dt from tb_user_log
) b
on
a.uid=b.uid
and
b.dt=date_add(a.dt,interval 1 day)
where
a.dt like "2021-11%"
group by
a.dt;
easy ~~