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

SELECT
    COUNT(DISTINCT q2.date, q2.device_id) / COUNT(DISTINCT q1.date, q1.device_id) AS avg_ret
    /*排除用户在同一天多次提交*/
FROM
    question_practice_detail AS q1
    LEFT OUTER JOIN question_practice_detail AS q2 ON q1.device_id = q2.device_id/*设备相同*/
    AND DATEDIFF(q2.date, q1.date) = 1;/*相差一天*/

思路解释

1）从总表中拿出某一日的刷题次数，接着再从表中拿出某一日和次日都刷题的次数

！同一用户可能多次刷题，所以存在多个id记录。

2）连续刷题频率=某一次和次日都刷题的人数/某一日刷题的人数，

所以在除的时候还需要考虑去重（从次数→人数）。一个用户某天多次刷题只需要记录一次，即同一天只需要记录一个设备ID

具体知识点

通过建立自联表筛选出前后两天活跃的人，q1为前一日刷题的人，q2为两日都在刷题的人，两者建立连接的条件是同一用户的唯一的设备id（筛出两天都刷题的人，以及日期相差1天（DATEDIFF）。

多列去重，DISTINCT后跟多列时，对列的组合去重。这里去除同一天内多次刷题的用户。