SQL29 计算用户的平均次日留存率

原题链接

【描述】

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

【示例】：question_practice_detail

【题目分析】摘自题解区"Reg333"的题解

所谓次日留存，指的是同一用户（在本题中则为同一设备，即device_id）在当天和第二天都进行刷题。注意，在这题我们不关心同一用户（设备）在这天答了什么题、答题结果如何，只关心他是否答题，因此对于这题来说存在重复的数据（如下图红框所示），需要使用 DISTINCT 去重。

avg_ret = 两天都在线的设备数 / 第一天的设备总数

SELECT
  COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
  (
    SELECT
      DISTINCT device_id, date
    FROM
      question_practice_detail
  ) as q1
LEFT JOIN 
  (
    SELECT
      DISTINCT device_id, date
    FROM
      question_practice_detail
  ) AS q2 
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

注意，MySQL中 COUNT 在对列进行计数时不统计值为 null 的条目

【心得】
利用 LEFT JOIN 进行自联结，当符合 q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day) 这个条件时（device_id 自然是恒成立的，所以关键还是看 DATE_ADD 成不成立）， q2 的 device_id 和 date 自然不为 null，也就是两天都在线的设备；当不符合这个条件时，q2 的 device_id 为 null，COUNT 也不会进行计算，这样一来，COUNT(q2.device_id) 计算的永远都是正确的两天都在线的设备数量；而第一天的设备数量，即为 COUNT(q1.device_id)

【left join 之后的一个临时表的效果示例：】

q1.device_id	q1.date	q2.device_id	q2.date
2315	2021-08-13	2315	2021-08-14
2315	2021-08-14	2315	2021-08-15
2138	2021-05-03	null	null
3214	2021-05-09	null	null
3214	2021-06-15	null	null