题解 | #计算用户的平均次日留存率#

首先来解释一下题目：

题目要求计算某个用户在第一日答题以后第二题继续答题的平均概率，简单举个例子就是，假设用户A在1月1日答题以后，1月2日继续答题，而表中只有这两条数据，那留存率就是1/2=0.5。

我在第一次做的时候忽略了表中含有的重复数据，比如device_id=2315，date=2021-08-13的数据是有两条的，那如果表中有这两条数据再加一条device_id=2315，date=2021-08-14的数据，实际上需要根据device_id和date两个字段做一个去重，最终的留存率应该是1/2=0.5，而不是2/3。这点是需要注意的。

答题步骤：

要计算最终的留存率实际上就是找出计算留存率的分子和分母分别是多少，然后做除法即可。

• 计算分母：如前文所述，分母即根据device_id和date两个字段去重后的数据条数，最终应该是10条。

SELECT COUNT(DISTINCT device_id, date)FROM question_practice_detail

• 计算分子：找到满足的留存率条件的数据条数，这里可以使用EXISTS来查找，首先需要满足device_id相等，其次，表中需要有date为当前数据下一日的数据，在下面的SQL中体现即存在t1.device_id = t2.device_id并且有且DATE_ADD(t1.date, INTERVAL 1 DAY) = t2.date（即有下一日的答题记录），最终结果需要根据device_id和date两个字段去重，最终应该是3条。

    SELECT COUNT(DISTINCT device_id, date)
    FROM question_practice_detail t1
    WHERE EXISTS(
        SELECT 1
        FROM question_practice_detail t2
        WHERE t1.device_id = t2.device_id AND DATE_ADD(t1.date, INTERVAL 1 DAY) = t2.date)

• 汇总代码：分子/分母=3/10=0.3

SELECT (
    SELECT COUNT(DISTINCT device_id, date)
    FROM question_practice_detail t1
    WHERE EXISTS(
        SELECT 1
        FROM question_practice_detail t2
        WHERE t1.device_id = t2.device_id AND DATE_ADD(t1.date, INTERVAL 1 DAY) = t2.date)
       ) / (
   SELECT COUNT(DISTINCT device_id, date)
   FROM question_practice_detail)
AS avg_ret;