【SQL 02 计算用户的平均次日留存率】

题目描述

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

问题联系记录表 question_practice_detail:

id	device_id	quest_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-06-10
3	6543	113	right	2021-06-15
4	2316	116	right	2021-05-13
5	217	118	right	2021-08-13

最终输出样式：

avg_ret
0.3000

题目分析

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

次日留存率=去重的数据表中符合次日留存的条目数目 / 去重的数据表中所有条目数目

具体而言，使用两个子查询，查询出两个去重的数据表，并使用条件（q2.date应该是q1.date的后一天）进行筛选，如下所示（数据未显示完全，从左至右顺序，列表名为 q1.device_id, q1.date, q2.device_id, q2.date)

因为使用的是q1左联q2，所以q1的所有信息是显示的；而q2中只显示留存的信息，否则为null。
最后，分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率.

SQL实现

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)

Tip：MySQL中 COUNT在对列进行计数时不统计值为 null的条目

Knowledge Point 知识点

1.MySQL DATE_ADD() 函数

定义：DATE_ADD() 函数向日期添加指定的时间间隔
语法：DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数是时间单位。

type 参数可以是以下值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

2.MySQL COUNT 函数

COUNT(*) 函数返回在给定的选择中被选的行数。
COUNT(column_name) 函数返回指定列的值的数目（NULL 空值不计入）

Reference

牛客网练习
SQL在线速查手册