题解 | 统计复旦用户8月练题情况

解析：
建议按照题目一步一步的 将要求编写下来，分成不同的表 单独对这些表进行操作 思路会更清晰
1.统计回答的题目数
需要找到关键的字段 特征是 答了题就有记录 你没有答题就没有是null的情况
再结合case函数进行搜索 count(case when result is not null then 1 else null end ) as question_cnt
2.需要求答对了多少题目
sum(case when result = 'right' then 1 else 0 end ) as right_question_cnt 
select
t1.device_id,t1.university,
count(case when result is not null then 1 else null end ) as question_cnt,
sum(case when result = 'right' then 1 else 0 end ) as right_question_cnt 
from 
(select device_id,university
from user_profile 
where university = '复旦大学') as t1
left join
(select device_id,result
from question_practice_detail 
where month(date) = '8') as t2
on t1.device_id = t2.device_id
group by t1.device_id;

==================
直接使用原表
select t1.device_id,t1.university, 
count(case when result is not null then 1 else null end ) as question_cnt, 
sum(case when result = 'right' then 1 else 0 end ) as right_question_cnt 
from user_profile as t1 
left join 
(select device_id,result from question_practice_detail where month(date) = '8') as t2
on t1.device_id = t2.device_id 
where  t1.university = '复旦大学' 
group by t1.device_id;