当前位置:网站首页>Daily sql - judgment + aggregation
Daily sql - judgment + aggregation
2022-08-11 07:14:00 【Eating too much sugar will not gain weight】
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
边栏推荐
猜你喜欢
随机推荐
矩阵分析——微分、积分、极限
一个小时快速熟悉MySQL基本用法
The ramdisk practice 1: the root file system integrated into the kernel
抖音API接口
一种用于EEG超扫描研究的分析流程
《Generative Adversarial Networks》
MySQL之CRUD
求过去半年内连续30天以上每天都有1000元以上成交的商铺
每日sql -用户两天留存率
Attitude solution - gyroscope + Euler method
八股文之mysql
HCIP-BGP的选路实验
HCIP MPLS/BGP Comprehensive Experiment
grep、sed、awk
HCIA experiment
maxwell 概念
HCIP experiments (pap, chap, HDLC, MGRE, RIP)
训练分类器
Top20括号匹配
Find the shops that have sold more than 1,000 yuan per day for more than 30 consecutive days in the past six months