【今日刷题】SQL29 计算用户的平均次日留存率

题目

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

• 题目描述如图：

  
  题目描述

分析

• 根据题意，需要求出用户在某天刷题后第二天还会再刷题的概率。我们首先需要找出所有刷题记录以及第二天还会再刷题的记录，因此我们对question_practice_detail表格进行连接操作。代码如下：

# 对表进行自连接操作，q1代表所有刷题记录。q2左连接匹配，找出第二天还会再刷题的记录
select * 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)

• 之后用第二天还会刷题记录的数目，除以所有刷题记录的数目，计算出平均次日留存率。代码如下：

# 用count(q2.device_id) / count(q1.device_id) 替换上面代码的*
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)