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

select
    up.device_id,
    up.university,
    count(qpd.question_id) question_cnt,
    sum(if(qpd.result = 'right', 1, 0)) right_question_cnt
	# sum嵌套if达到条件求和的作用
from
    user_profile up
    left join question_practice_detail qpd on up.device_id = qpd.device_id
    and left(qpd.date, 7) = '2021-08'
where
    up.university = '复旦大学'
group by
    1,
    2