题解 | 统计复旦用户8月练题情况
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
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 = '复旦大学'
