当前位置:网站首页>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
边栏推荐
猜你喜欢
随机推荐
《Show, Attend and Tell: Neural Image Caption Generation with Visual Attention》论文阅读(详细)
防火墙-0-管理地址
Top20 bracket matching
每日sql -用户两天留存率
华为防火墙-7-dhcp
HCIP experiments (pap, chap, HDLC, MGRE, RIP)
矩阵分析——矩阵分解
Conference OA Project My Conference
iptables nat
HCIP OSPF/MGRE综合实验
亚马逊获得AMAZON商品详情 API 返回值说明
安装cuda10.2下paddlepaddle的安装
mmdetection的安装和训练、测试didi数据集的步骤(含结果)
八股文之redis
图的拉普拉斯矩阵
拼多多API接口大全
HCIA experiment
HCIP MGRE\OSPF Comprehensive Experiment
每日sql--统计员工近三个月的总薪水(不包括最新一个月)
HCIP MPLS/BGP Comprehensive Experiment