题目来源：牛客网：SQL29 计算用户的平均次日留存率

题目描述

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

示例：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
……

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

avg_ret
0.3000

解法1（自己解法）

SELECT AVG(is_ret)
FROM (
    SELECT  DISTINCT device_id
       ,date
       ,IF( (device_id,date) IN ( SELECT device_id,DATE_ADD(date,INTERVAL 1 DAY) FROM question_practice_detail ),1,0 ) AS is_ret
    FROM question_practice_detail
) AS t

解法2（最简洁的解法）

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

解法3

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)

继续做题！

-- 数据生成代码
drop table if  exists `question_practice_detail`;
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');