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

SELECT
    up.device_id,
    up.university,
    SUM(IF(qpd.question_id IS NULL,0,1)) AS question_cnt,
    SUM(IF(qpd.question_id IS NOT NULL AND qpd.result = 'right',1,0)) AS right_question_cnt
FROM user_profile up
INNER JOIN question_practice_detail qpd USING (device_id)
WHERE MONTH(qpd.date) = 8
GROUP BY up.device_id, up.university
HAVING up.university = '复旦大学'