SQL29 计算用户的平均次日留存率
描述
题目:现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。请你取出相应数据。
思路:
①明确留存率怎么计算,关注什么:需要找到当天刷题并且第二天也刷题的用户
②留存率计算公式:
次日留存率=去重数据表中符合次日留存的条目数目\去重的数据表中所有条目数目
③筛选条件:q2.date是q1.date的后一天
代码:
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)
关键部分:
①两个子查询,查询出两个去重的数据表
②q1左连接q2,保证q1所有信息全显示,q2只显示留存的
③q1,q2为同一张表的两次映射,q1表示第一天刷题,q2表示第二天刷题
④count在对列进行计数时,不统计值为null的条目
⑤DATE_ADD语法:DATE_ADD(date,interval value unit)
date:想进行加法操作的日期
value:想加上的数值,可以是整数或者浮动的数字
unit:时间单位,表示你要加上的时间单位,例如second、minute、day等