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

# select up.device_id, up.university, count(question_id) question_cnt, sum(if (qpd.result = 'right', 1, 0)) right_question_cnt
# from user_profile up
# left join question_practice_detail qpd on up.device_id = qpd.device_id
# where up.university = '复旦大学'
# and qpd.date >= '2021-08-01' and qpd.date <= '2021-08-31'
# group by up.device_id


select up.device_id, up.university, count(question_id) question_cnt, sum(if (qpd.result = 'right', 1, 0)) 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
group by up.device_id

判断月份，可以只取日期中的月份值