SQL34. 统计复旦用户8月练题情况

描述

示例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');

输出:

3214|复旦大学|3|0
4321|复旦大学|0|0

原站题解

select
t1.device_id,
t1.university,
sum(case when t2.result is not null then 1 else 0 end),
sum(case when t2.result = 'right' then 1 else 0 end)
FROM user_profile t1
LEFT JOIN question_practice_detail t2 
ON t1.device_id = t2.device_id and MONTH(t2.date) = '08'
where t1.university = '复旦大学'
group by t1.device_id

select 
t1.device_id, t1.university, 
count(question_id) as question_cnt,
sum(if(t2.result = 'right',1,0) ) as right_question_cnt
from user_profile as t1
left join 
question_practice_detail as t2
on t1.device_id = t2.device_id  and month(t2.date) = 8
where t1.university = '复旦大学'
group by t1.device_id

select 
       t1.device_id,
       t1.university,
       COUNT(t2.question_id) AS question_cnt,
       SUM(IF(t2.result = 'right',1,0)) AS right_question_cnt
from user_profile t1
left JOIN question_practice_detail t2
ON t1.device_id = t2.device_id
WHERE university = '复旦大学'
and (month(t2.date) =8 or month(t2.date) is null)
GROUP BY t1.device_id

select a.device_id,
a.university,
count(b.question_id)question_cnt,
sum(if(b.result = 'right',1,0))
from user_profile a
left JOIN
    question_practice_detail b on a.device_id = b.device_id
    and month(b.date) = 8
where a.university = '复旦大学'
group by a.device_id

SELECT
    up.device_id,
       up.university,
       count(ifnull(qpd.question_id, null)) AS question_cnt,
       count(if(qpd.result = 'right', 1, null)) AS right_question_cnt
from user_profile AS up
LEFT JOIN question_practice_detail qpd on up.device_id = qpd.device_id
AND YEAR(qpd.date) = '2021' AND MONTH(qpd.date) = '08'
WHERE up.university = '复旦大学'
group by up.device_id