题解 | #统计每个学校各难度的用户平均刷题数#
统计每个学校各难度的用户平均刷题数
https://www.nowcoder.com/practice/5400df085a034f88b2e17941ab338ee8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3D%25E7%25AE%2597%25E6%25B3%2595%25E7%25AF%2587%26topicId%3D295
select university, difficult_level, round(count(question_practice_detail.question_id) / count(distinct(question_practice_detail.device_id)), 4) as 'avg_answer_cnt' from user_profile inner join question_practice_detail on user_profile.device_id = question_practice_detail.device_id inner join question_detail on question_detail.question_id = question_practice_detail.question_id group by university, difficult_level
思路:
1、先分组,按照学校、不同难度
2、三表内联, 没有null的情况
3、求 用户平均答题量 = 答题总数 (question_practice_detail.question_id)/ 答题人数 (question_practice_detail.device_id 去重一下)
round(count(question_practice_detail.question_id) / count(distinct(question_practice_detail.device_id)), 4)