SQL笔记8：留存率问题解决

---

前言

主要解决留存率问题：
题目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 ~~