SQL29. 计算用户的平均次日留存率

描述

示例1

输入:

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
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
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
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');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出:

0.3000

原站题解

SELECT COUNT(distinct q2.device_id,q2.date)/COUNT(distinct q1.device_id,q1.date) as avg_ret
FROM question_practice_detail q1
LEFT  JOIN question_practice_detail q2
ON q1.device_id=q2.device_id and datediff(q1.date,q2.date)=1

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) #date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

select COUNT(b.device_id)/count(a.device_id) AS avg_ret
from 
(select distinct device_id,date from question_practice_detail) a
left JOIN
(select DISTINCT device_id,DATE FROM question_practice_detail) b
on a.device_id=b.device_id and b.date=date_add(a.date,INTERVAL 1 DAY)

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 join question_practice_detail as q2
on q1.device_id = q2.device_id and DATEDIFF(q1.date,q2.date)=1;

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


-- distinct 关键字作用于所有的列，不仅仅是跟在其后的那一列
-- (distinct q2.device_id,q2.date)，同时distinct 用户Id和date，是防止同一个人一天多次答题
-- eg:2138  111  wrong  2021-05-03
--    2138  113  right  2021-05-03
--    2138  113  right  2021-05-09
-- datediff 函数：SELECT DATEDIFF('2017-08-08','2017-08-17')  答案，-9