题解 | #统计每个学校的答过题的用户的平均答题数#
统计每个学校的答过题的用户的平均答题数
http://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5
这道题目很有意思，原本以为很简单的一道题目，没想到内有乾坤（也可能是本人想复杂了）
#第一步：找出各高校的用户答题总次数
##第二步：找出各高校真正答题的人数
#2.1 真正答题的device_id
#2.2 挖掘人数
#第三步：两表连接，做除法将avg_answer_cnt求出来
SELECT up.device_id, up.university ,COUNT(dq.question_id) "questions" FROM user_profile up LEFT JOIN question_practice_detail dq ON up.device_id = dq.device_id GROUP BY up.university
##第二步：找出各高校真正答题的人数
#2.1 真正答题的device_id
SELECT DISTINCT up.device_id, up.university FROM user_profile up LEFT JOIN question_practice_detail dq ON up.device_id = dq.device_id WHERE dq.question_id IS NOT NULL
#2.2 挖掘人数
SELECT device_id,university, COUNT(university) "peoples" FROM (SELECT DISTINCT up.device_id, up.university FROM user_profile up LEFT JOIN question_practice_detail dq ON up.device_id = dq.device_id WHERE dq.question_id IS NOT NULL) t GROUP BY university
#第三步：两表连接，做除法将avg_answer_cnt求出来
SELECT t1.university, ROUND(questions / peoples , 4) "avg_answer_cnt" FROM (SELECT up.device_id, up.university ,COUNT(dq.question_id) "questions" FROM user_profile up LEFT JOIN question_practice_detail dq ON up.device_id = dq.device_id GROUP BY up.university) t1 JOIN (SELECT device_id,university, COUNT(university) "peoples" FROM (SELECT DISTINCT up.device_id, up.university FROM user_profile up LEFT JOIN question_practice_detail dq ON up.device_id = dq.device_id WHERE dq.question_id IS NOT NULL) t GROUP BY university ) t2 ON t1.device_id = t2.device_id ORDER BY t1.university
查看30道真题和解析
