错题丨N日留存率问题，第二天会来问题

用户在某天刷题后 第二天还会再来 刷题的平均概率。 正确答案↓

SELECT AVG(IF(DATEDIFF(date2,date1)=1,1,0))as avg_ret 
FROM(
SELECT DISTINCT device_id,
date date1,
lead(date) over(PARTITION by device_id ORDER BY date) as date2
FROM (SELECT DISTINCT device_id,date from question_practice_detail)a)b

如何理解

从尾巴到开头的理解法为↓

首先找两个表，第一个是去重的id和时间表，第二个是date1和date2以及去重的ID表 最后是从这两个表中找出时间1和时间2，然后用if赋值间隔为1的赋值间隔为其他的赋值另外的值，最后聚合函数求平均概率

从开始到结尾的理解法为↓ 求时间间隔为1的平均概率 那么就为 AVG(IF(DATEDIFF(date2,date1)=1,1,0))as avg_ret from （有date1和date2） 其次，找出date1date2，

SELECT DISTINCT device_id,
date date1,
lead(date) over(PARTITION by device_id ORDER BY date) as date2

最后，从原始表中引入数据，（有date1和date2）fromSELECT DISTINCT device_id,date from question_practice_detail

为什么要distinct id和时间不直接引用原始表呢，直接引用原始表也行，因为distinct后数据少出来比较快 三部分连接就变成了正确↓

SELECT AVG(IF(DATEDIFF(date2,date1)=1,1,0))as avg_ret 
FROM(
SELECT DISTINCT device_id,
date date1,
lead(date) over(PARTITION by device_id ORDER BY date) as date2
FROM (SELECT DISTINCT device_id,date from question_practice_detail)a)b

引用的表记得赋值。

举一反三:这不仅可以求次日留存率，更加可以求某日留存率，

而且记住聚合函数会一个一个找字段来聚合，这就是它称为聚合函数的作用之一，如这里的avg会算全部符合条件的数据的平均值，所以平均率就算出来了