SQL实现留存率计算

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

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#相差一天

1.表结构

示例：question_practice_detail

id	device_id	question_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
8	3214	112	wrong	2021-05-09
9	3214	113	wrong	2021-08-15
10	6543	111	right	2021-08-13
11	2315	115	right	2021-08-13
12	2315	116	right	2021-08-14
13	2315	117	wrong	2021-08-15
14	3214	112	wrong	2021-08-16
15	3214	113	wrong	2021-08-18
16	6543	111	right	2021-08-13