Daily sql - judgment + aggregation

Current operations want to know the total number of questions and the number of questions answered correctly by each user of Fudan University in August, please take out the corresponding detailed data. For users who have not practiced in August, the number of questions answered will be returned.0

data

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,'Peking University',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'Fudan University',4.0,15,5,25);INSERT INTO user_profile VALUES(3,6543,'female',20,'Peking University',3.2,12,3,30);INSERT INTO user_profile VALUES(4,2315,'female',23,'Zhejiang University',3.6,5,1,2);INSERT INTO user_profile VALUES(5,5432,'male',25,'Shandong University',3.8,20,15,70);INSERT INTO user_profile VALUES(6,2131,'male',28,'Shandong University',3.3,15,7,13);INSERT INTO user_profile VALUES(7,4321,'male',28,'Fudan University',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');

Resolved

selectb.device_id,b.university,sum(case when (a.question_id is not null or a.question_id != '') and substr(a.date,6,2) ='08' then 1 else 0 end ) as question_cnt,sum(case when a.result = 'right' and substr(a.date,6,2) ='08' then 1 else 0 end ) as right_question_cntfrom question_practice_detail aright join user_profile b on b.device_id = a.device_idwhere b.university = 'Fudan University'group by b.device_id,b.university