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

思路一： 找出用户连续两天的刷题情况，比如用户1在2021-08-14和2021-08-15都刷题了，那就是连续两天刷题，用户2在2021-08-14刷题了，但2021-08-15没刷题，那就是没有连续两天刷题。

做法：

• 将第一天和第二天的表连接（由于看的是连续刷题的比例，所以没有连续两天刷题的记录也要保留，用left join）；
• 要实现两张表日期差一天，可以用datediff、date_add、date_sub

select
  distinct a.device_id,
  a.date as date1,
  b.date as date2
from
  question_practice_detail a
  left join question_practice_detail b 
  on a.device_id = b.device_id
  and datediff(b.date, a.date) = 1
-- date_add(a.date, interval 1 day) = b.date
-- date_sub(b.date, interval 1 day) = a.date

得到的结果为：

接下来就很简单了，date1列是第一天刷题的情况，date2是第二天刷题的情况，两列求比值就是连续两天刷题的平均概率。

select
  round(count(date2) / count(date1), 4) as avg_ret 
from
  (
    select
      distinct a.device_id,
      a.date as date1,
      b.date as date2
    from
      question_practice_detail a
      left join question_practice_detail b 
      on a.device_id = b.device_id
      and date_add(a.date, interval 1 day) = b.date
  ) c

注意：对date计数不用去重，否则得到的就是天数而不是人数了。

思路二： 直接用窗口函数将用户前后两天的刷题情况拼接起来 注意：这里拼出来的不一定是连续两天，例如用户2在2021-08-11和2021-08-14刷题，中间没有其他记录，窗口函数也会将这两条记录拼起来！所以count里面要区分下情况~

select
  round(
    count(if(datediff(date2, date1) = 1, date2, null)) / count(date1),
    4
  ) as avg_ret
from
  (
    select
      distinct device_id,
      date as date1,
      lead(date, 1) over(
        partition by device_id
        order by
          date asc
      ) as date2
    from
      (
        select
          distinct device_id,
          date
        from
          question_practice_detail
      ) a
  ) b