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

描述

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

答题

SELECT

up.device_id,

university,

COUNT(question_id) AS question_cnt,

(SELECT COUNT(result)
 FROM question_practice_detail qpd
 WHERE device_id = up.device_id AND result = 'right'
 ) AS right_question_cnt
 

FROM user_profile up

LEFT JOIN question_practice_detail qpd USING(device_id)

WHERE MONTH(date) = 8 AND university = '复旦大学'

GROUP BY device_id