留存率计算问题

首先研究 牛客SQL题目中的两道留存率计算问题，即第68题和第70题。
留存率的计算一般只用涉及用户ID和时间两个字段数据。

问题1——统计一下牛客新登录用户的次日成功的留存率（总体留存率）
第一种思路
用左联结求总的留存率

select round(count(l.user_id)/count(a.user_id),3) p
from
(select user_id,min(date) first_day
from login group by user_id) a
left join
login l
on a.user_id=l.user_id and l.date=date_add(a.first_day,interval 1 day);

第二种思路
子查询方法

select round(count(distinct user_id)*1.0
	/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date) in 
	(select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) 
	from login group by user_id);
#分母直接可以用表记录的不同user_id的数量了，只需分子上对+1天进行统计

问题2——统计一下牛客每个日期新用户的次日留存率（分日期留存率）
第一种思路
自联结后列出 first_day和second_day两列，再根据这两个日期的数量计算留存率，列出second_day列的方法很多。

select o.date,coalesce(r.p,0.000) p
from
(select distinct date from login) o
left join
(
    select t.first_day,round(sum(if(t.second_day is null,0,1))/count(*),3) p
    from 
    (select a.user_id,a.first_day,l.date as second_day from
        (select user_id,min(date) first_day from login group by user_id) a
        left join login l on a.user_id=l.user_id 
        and l.date=date_add(a.first_day,interval 1 day) ) t
    group by t.first_day
) r
on o.date=r.first_day
order by date;

第二种思路
添加一列判断时间间隔，后面可以直接根据时间间隔来选符合的记录，然后计算留存率。这个做法没见到评论区有。

with t as 
(select user_id,date,
datediff(date,first_value(date) over(partition by user_id order by date 
rows between unbounded preceding and unbounded following)) as day_diff
from login)
#尝试用下with … as …
select l.date,coalesce(t.p,0.000) p
from 
(select distinct date from login) l
left join
(
select a.date,round(count(b.date)/count(a.date),3) p
from
    (select date ,user_id from t where day_diff=0) a
    left join 
    (select date ,user_id from t where day_diff=1) b
    on b.user_id=a.user_id
group by a.date) t
on l.date=t.date；

第三种思路
从评论区找到的最简单的高赞答案
#分母：当前日期新用户的特征是 当前日期=该用户所有登录日期的最小值
#分子：当前日期作为前一天有该用户的登录记录 并且是第一次登录

select date,
        ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day) 
             from login)  and (user_id,date)in (select user_id,min(date)from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

第四种思路
学习了这个链接，做一下这个题
用户留存分析
这个链接中有两个表，一个是用户注册时间表，在这里就可以用首个时间来构建此表，一个是除了首个时间其它的登陆行为表，因此这里用with as先构建两个表，再计算留存率。

with t as(
select user_id,min(date) reg_time from login group by user_id
order by user_id),
     t2 as(
     select user_id,date login_time from login where (user_id,date) not in
     (select user_id,min(date) from login group by user_id)
     )

select l.date,coalesce(a.p,0.000) p
from
(select distinct date from login) l
left join
(
select t.reg_time,
round(sum(datediff(login_time,reg_time)=1)/count(distinct t.user_id),3) p
from t left join t2 on t.user_id=t2.user_id
group by t.reg_time) a
on l.date=a.reg_time
order by l.date;

这里get到一个知识点：
SUM(条件表达式)，如果记录满足条件表达式就加1，统计满足条件的行数
COUNT(条件表达式)，不管记录是否满足条件表达式，只要非NULL就加1
用以解释为何计算用户留存数时候用sum不用count。

其实方法很多，每次写都不一定会完全相同。