题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
http://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
题目分为几个阶段，根据条件要求：
① 在user_profile表中找出属于复旦大学的相关用户信息，记为表一
select
device_id,
university
from user_profile
where university = "复旦大学"
② 在question_practice_detai表中找出属于8月份题目的相关信息，记为表二
select
device_id,
question_id,
result,
date
from question_practice_detail
where month(date) = 8
③ 使用left join将表一和表二合并（因为没有答题的同学也需要计入），记为表三
select
up.device_id,
university,
question_id,
result
from (select
device_id,
university
from user_profile
where university = "复旦大学") as up
left join (select
device_id,
question_id,
result,
date
from question_practice_detail
where month(date) = 8) as qpd
on up.device_id = qpd.device_id
④ 根据表三，使用group by对device_id进行分组，count函数统计question_id个数为总题目数；sum函数汇总即使用case when 条件选择答对的题目进行统计，即可获得最后结果
select
device_id,
university,
count(question_id) as question_cnt,
sum(case when result = "wrong" then 0
when result IS NULL then 0
else 1 end) as right_question_cnt
from (select
up.device_id,
university,
question_id,
result
from (select
device_id,
university
from user_profile
where university = "复旦大学") as up
left join (select
device_id,
question_id,
result,
date
from question_practice_detail
where month(date) = 8) as qpd
on up.device_id = qpd.device_id ) as t
group by device_id, university
