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

select 
up.device_id,
up.university ,
sum(case when date_format(qpd.date,"%m") ='08' then 1 else 0 end)  question_cnt ,
sum(case when qpd.result = 'right' and date_format(qpd.date,"%m") ='08' then 1 else 0 end)
right_question_cnt from user_profile  up left join question_practice_detail  qpd on up.device_id = qpd.device_id  where up.university = '复旦大学'   group by up.device_id,up.university