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

SELECT t1.device_id,t1.university,
       COUNT(t2.question_id) AS question_cnt,
       COUNT(CASE WHEN t2.result="right" THEN t2.question_id ELSE NULL END) AS right_question_cnt
FROM user_profile AS t1
LEFT JOIN question_practice_detail AS t2
ON t1.device_id=t2.device_id AND MONTH(t2.date)=8
WHERE t1.university="复旦大学"
GROUP BY t1.device_id;