题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
http://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
描述
题目： 现在运营想要了解复旦大学的每个用户在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
