SQL39 Total number of practice questions in August 21

describe

Question: If you want to know the total number of users who have practiced the questions in August 2021 and the total number of times you have practiced the questions, please get the corresponding results.

Example: question_practice_detail

According to the example, your query should return the following results:

Example 1

enter:

drop table if exists `user_profile`;
drop table if  exists `question_practice_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
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'Beijing 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,'Beijing 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');

Copy the output:

3|12

Question answer:

select



    count(distinct device_id) as did_cnt,



    count(question_id) as question_cnt




from question_practice_detail




where date like "2021-08%"

AI writing code