SQL练习：计算用户的平均次日留存率（DATEDIFF）

用DATEDIFF简单的实现计算用户的平均次日留存率

• DATEDIFF函数介绍

用于计算计算两个DATE，DATETIME或TIMESTAMP值之间的天数

# 用法
DATEDIFF(date1,date2)
#例子
mysql> SELECT DATEDIFF('2023-01-30','2023-01-29'); 
+-------------------------------------+
| DATEDIFF('2023-01-30','2023-01-29') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set

mysql> SELECT DATEDIFF('2023-01-29','2023-01-30'); 
+-------------------------------------+
| DATEDIFF('2023-01-29','2023-01-30') |
+-------------------------------------+
|                                  -1 |
+-------------------------------------+
1 row in set

• 题目

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

示例：question_practice_detail
id device_id quest_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据示例，你的查询应返回以下结果：
avg_ret
0.3000

实现：

SELECT COUNT(DISTINCT q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1;

将两张表进行左外连接，连接规则是

• 用户id相同并且日期相差为1（确保连接的数据为第二天）

然后统计各字段的人数，因为一个用户可能同一天来刷题多次，所以用DISTINCT进行去重

第二天的人数除以第一天的人数，就可以看作平均次日留存率。