经典典中典：计算用户的平均次日留存率（自留）

首先放一下自己没有参考任何东西写出来的：

with tb as(
    select q1.device_id as d1 , q2.device_id as d2
    from question_practice_detail as q1 
    left join question_practice_detail as q2
    on q2.date = date_add(q1.date,interval 1 day) and q1.device_id = q2.device_id

)
select round(count(distinct(d2))/count(distinct(d1)),4) as avg_ret
from tb
where d2 is not null 

想的是取出两列，对应第一天和第二天的device_id，用于计数。写出这样代码的几个想法

1.保留前一天的所有数据，让第二天有数据的和前一天的匹配，所以使用left join；

2.使用date_add(q1.date,interval 1 day) 连接到第二天也有记录的device_id，同时也采用device_id作为连接键。

但是运行之后的结果恒为1，肯定是逻辑上出了问题，其实我自己也觉得取两列device_id做连接怪怪的，但是也一时间想不到太好的其他办法。

先看一下官方给出的正确答案：

SELECT 
    ROUND(
        SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) , 
        4
    ) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p1
LEFT JOIN 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p2
ON 
    p1.device_id = p2.device_id 
    AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);

乍一看，好像我自己写出来的和官方答案基本一样啊...可以注意到，表连接的部分是基本一致的，但是去重不一样！（问题就出在这里）还有计算留存率的公式不一样，我是自己写了个很奇怪的，官答是用优雅的case when来完成的，显然后者更好，简洁且准确。

我让豆包看了二者的区别，它给我举了个例子，数据如下：

以下是相差的地方：

我理解的官答的几个key points:

1.不单独select device_id作一个cte，再从cte作主查询；而是select from( select)直接做嵌套，而且直接在内层子查询里用distinct去重，这样避免了后续因为重复数据导致的麻烦。我的方法一开始也是在select的时候用distinct，但是后面变成了计算留存率的时候才用，可能两个都不对。

这里获得的启示是，在最主要的查询/计算层尽量只专注于取数/计算本身，避免无关的操作，可以把其放在嵌套的子查询中。

2.多用case when ... then 1 else 0 end这种类似“计数器”/“示性函数”的工具，在计算比率指标的时候非常好用！

虽然刚开始刷sql，但是对于“留存率”计算这个经典问题早有耳闻，貌似是大厂sql笔面试的重点，所以写个自用文档mark一下。