题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
SELECT
U.device_id,
U.university,
SUM(IF(Q.question_id IS NOT NULL, 1, 0)) AS question_cnt,
SUM(IF(Q.result = 'right', 1, 0)) AS right_question_cnt
FROM
user_profile AS U
LEFT JOIN question_practice_detail AS Q
ON U.device_id = Q.device_id
AND MONTH(date) = 08
WHERE
U.university = '复旦大学'
GROUP BY
U.device_id;