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

注意时间条件

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