求用户的平均次日留存率

求用户的平均次日留存率

---

一、题目

题目来源于牛客，难度为困难。★★★
现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率，请你取出相应数据。

二、解题思路与过程

1.解题思路

（1）根据提示可知，查询的列涉及到device_id和date列,与quest_id,result列无关。可以查看一下这两列数据。

select device_id,date --查看这两列的数据
from question_practice_detail

运行的部分结果为：
由上图可知，第7行和第13行重复，该表中有重复的数据，需要去重。
用户在某天刷题后第二天还会再来刷题的平均概率=表中符合次日留存的去重条目数/表中所有的去重条目数
（2）关键词为**“第二天”**，该用户前一天刷题，后一天仍刷题则为次日留存的数据。可以使用LAG函数将用户的刷题日期数据后移一位。

select device_id,date,
lead(date,1)over(partition by device_id order by date) date2-- 找出date列中位于本行前一列的值
from question_practice_detail
group by device_id, date

运行结果为

符合次日留存的去重条目数为第2行，3行，7行。使用where条件将其筛选出来，
并计数，运行结果为3，代码如下。

select count(*)
from
(select device_id,date,
lead(date,1)over(partition by device_id order by date) date2
from question_practice_detail
group by device_id, date) b
where date2-date=1 -- 筛选出date的前一个日期date2是否和date相差一天。

（3）找出分母，表中所有的去重条目数，先不计数。

select device_id,date
from question_practice_detail
group by device_id,date

运行的结果为

计数的运行结果为10，代码如下

 select count(*)
from
(select device_id,date
from question_practice_detail
group by device_id,date)b

2.解题过程

将（2）和（3）中的SQL写到一个里面，可以使用with…as…,最后的运行结果为3/10=0.3

with 
table1 as
(select device_id,date
from question_practice_detail
group by device_id,date
)

select 
count(*)/(select count(*) from table1 )
from
(select device_id,date,
lead(date,1)over(partition by device_id order by date) date2
from question_practice_detail
group by device_id, date) b
where date2-date=1

三、拓展

(1)关于前后函数-LAG和LEAD
返回位于当前行的前n行（LAG(expr,n）或（LEAD(expr,n))的expr的值。仍以本题为例,举例说明LAG函数的用法

select device_id,quest_id,result,date,
lag(date,1,"2023-2-1")over(partition by device_id order by date) lagdate1
from question_practice_detail

运行的部分结果如下其中绿色框里的是默认值“2023-2-1”，因为该行是某组内的第一行，不存在前面一行。
其他箭头数据代表组内取前一行的数据。

select device_id,question_id,result,date,
lag(date,2)over(partition by device_id order by date) lagdate1
from question_practice_detail

运行的部分结果如下

绿色框里的是默认值为None,因为该行是某组内的第一行和第二行，不存在前面二行。
其他箭头数据代表组内取前二行的数据。
(2) 易错点：where后面不能使用列的别名，原因在于SQL的执行顺序为先执行where，后执行select。
(3) 易错点：with as 中最后一个as 不用加逗号。

✓后期会继续更新用户留存率方面的题…