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

SELECT t0.device_id,
university,
COUNT(IF(month(date)=8,question_id,NULL)) as question_cnt,
SUM(CASE WHEN result = 'right' AND month(date)=8  THEN 1 ELSE 0 END ) as right_question_cnt

FROM user_profile t0
LEFT JOIN question_practice_detail t1
ON t1.device_id = t0.device_id
WHERE university = '复旦大学'
GROUP BY 1,2