题目描述

题目要求从question_practice_detail表中取出数据，计算用户在某天刷题后第二天还会再来刷题的留存率。表中包含 id、device_id（用户设备 ID）、question_id（题目 ID）、result（做题结果）、date（做题日期）等字段。

题目分析

本题核心是计算次日留存率，关键在于确定用户刷题日期和次日刷题情况。要通过对表中数据的处理和关联，筛选出符合次日留存条件的记录，进而计算留存率。这道题目的难点就在于如何准确找到“次日”使用记录。

两种解法思路详解

解法一：窗口函数

在这种解法中，我们会用到LEAD()函数来获取每个设备某天刷题后第二次刷题的日期，再通过条件判断DATEDIFF(next_date, date) = 1来识别该设备第二次刷题的日期是否就是“次日”，从而实现找到“次日”使用记录。

解法二：表连接

这种解法则是通过将同一设备的某天记录和次日记录关联起来以检查该设备次日是否使用，再用COUNT()函数统计次日使用的记录数及总记录数，相除得到留存率。

两种解法思路总结

解法二相较于解法一可能更直观一些，对于SQL的新手朋友们可能比较友好，也相对不容易出错。解法一的代码则相对更简洁一些。

从数据中可以看到，同一个设备可能会在同一天刷多道题目，因此这两种解法有一个共同需要注意的点，即需要删除重复值，否则会影响次日留存率的计算。

两种解法代码详解

解法一：窗口函数

SELECT COUNT(IF(DATEDIFF(next_date, date) = 1, 1, NULL)) / COUNT(date) AS avg_ret
FROM (
    SELECT device_id, date, 
           LEAD(date) OVER (PARTITION BY device_id ORDER BY date) AS next_date
    FROM (
        SELECT DISTINCT device_id, date 
        FROM question_practice_detail
    ) AS unique_id
) AS ques

关键点：

• 用DISTINCT去重，确保每个设备每天只记录一次;
• LEAD(date)按设备分组（PARTITION BY device_id）找下一天日期，ORDER BY date确保顺序正确;
• DATEDIFF(next_date, date) = 1判断是否为次日使用，计算留存率。

解法二：表连接

SELECT COUNT(second_day) / COUNT(date) AS avg_ret
FROM (
    SELECT DISTINCT device_id, date
    FROM question_practice_detail) AS d1
LEFT JOIN (
    SELECT DISTINCT device_id, DATE_ADD(date, INTERVAL 1 DAY) AS second_day
    FROM question_practice_detail) AS d2
ON (d1.device_id = d2.device_id) AND (d1.date = d2.second_day)

关键点

• 用两张去重后的表（d1和d2）连接，d1是基准日期，d2是次日日期。
• 左连接后，second_day字段为空说明该设备次日没有再次登陆刷题，因此可以直接COUNT(second_day)来计算次日留存的设备数