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

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

• 数据对象：一个用户一天做题为一条记录，“第二天还会再来”表示同一个用户在第二天也做了题目，

• 数据案例：用户A-2022-03-18，用户A-2022-03-19，出现了第二天还会再来

• 难点：

  ① 注意去重，同一个用户同一天多次做题，这个只作为一条记录，需要同时对（device_id,date）进行去重（存在一个有些不确定的地方：同一个人多次“第二天再来”是否只算一次，是以人为计量，还是以人+日期进行计量？题目是以人+日期计量）

  ② “第二天”这个概念计算，可以通过MySql中的日期函数data_add(date, interval 'num' 'type'),num表示数字，type表示日期类型，如day、month、year等

  ③确定再来的记录数量，通过表关联（left join ... on ...），左表为第一天做题，右表为第二天做题，可以将第二天做题时间-1，此时当设备id，做题日期相同时，如果两表都有数据，则符合“第二天还会再来”,如果两表有一个不存在，则不符合条件

在开始的时候，我是以人为计量进行计算，所以先进行了关联，最终去重是device_id，计算错误；又想到了①，对（device_id,date）。 初始sql如下：

select
  (t1.num / t2.num) as avg_ret
from
  ( -- 计算第二天再来的记录数量
    select
      count(distinct a.device_id, a.date) as num
    from
      question_practice_detail a
      left join question_practice_detail b on a.device_id = b.device_id
      and a.date = date_add(b.date, interval -1 day)
    where
      b.device_id is not null
  ) t1,
  ( -- 计算总记录数量
    select
      count(distinct device_id, date) as num
    from
      question_practice_detail
  ) t2;

后期可以根据sql 的执行计划继续再次优化