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

SELECT u.device_id, university,
sum(case when result IS NULL then 0 else 1 end) as question_cnt,
sum(case when result = 'right' then 1 else 0 end) as right_question_cnt
FROM user_profile as u LEFT JOIN question_practice_detail as q
ON u.device_id=q.device_id
WHERE university='复旦大学' and (month(date)=8 or date is null)
GROUP BY u.device_id;