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

用case的办法，case换if能更简单一些。

SELECT 
t1.device_id AS device_id,
ANY_VALUE(t1.university) AS university,
SUM(CASE WHEN t1.device_id IN (t2.device_id) THEN 1
ELSE 0 END),
SUM(CASE WHEN t1.device_id IN (t2.device_id)
AND t2.result = 'right' THEN 1
ELSE 0 END)
FROM
user_profile AS t1
LEFT JOIN   
question_practice_detail AS t2 
ON t1.device_id = t2.device_id AND MONTH (date) = 8
WHERE university = '复旦大学'
GROUP BY
device_id