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

select 
up.device_id,
up.university,
count(qpd.question_id) as question_cnt	,
count(
    case 
when qpd.result = 'right' then 1
else null
end
) as right_question_cnt
from user_profile as up 
left join question_practice_detail as qpd on up.device_id = qpd.device_id
and month(qpd.date) = 8
where university = '复旦大学' 
group by up.device_id 

# 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况
# 每个用户分类，练习总题数，正确题数

# select 
# t1.device_id, t1.university, 
# count(question_id) as question_cnt,
# sum(if(t2.result = 'right',1,0) ) as right_question_cnt
# from user_profile as t1
# left join 
# question_practice_detail as t2
# on t1.device_id = t2.device_id  and month(t2.date) = 8
# where t1.university = '复旦大学'
# group by t1.device_id