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

SELECT up.device_id,
       up.university,
       COUNT(qpd.result)                                          AS question_cnt,
       SUM(CASE WHEN qpd.result = 'right' THEN 1 ELSE 0 END) AS right_question_cnt
FROM user_profile up
LEFT JOIN question_practice_detail qpd ON up.device_id = qpd.device_id AND date_format(qpd.date, '%Y-%m') = '2021-08'
WHERE up.university = '复旦大学'
AND EXISTS (SELECT 1 FROM question_practice_detail WHERE device_id = up.device_id AND date_format(date, '%Y-%m') = '2021-08')
GROUP BY up.device_id, up.university

UNION

SELECT device_id, university, 0 AS question_cnt, 0 AS right_question_cnt
FROM user_profile
WHERE university = '复旦大学'
AND NOT EXISTS (SELECT 1 FROM question_practice_detail WHERE device_id = user_profile.device_id AND date_format(date, '%Y-%m') = '2021-08')