题解 | #月均完成试卷数不小于3的用户爱作答的类别#
月均完成试卷数不小于3的用户爱作答的类别
https://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845
/* --1.每个用户的总作答次数(聚合uid) --2.选出月均作答次数大于2的用户id(where语句筛选;用distinct去重,筛选出答题的月份;最后用count统计答题次数和答题的月数) --3.join表格得到这些用户的作答类型和作答次数, --4.根据作答类型聚合最终结果,按照作答次数降序排序 */ with tem as ( select uid, count(score)/count(distinct month(submit_time)) nums from exam_record where score is not null group by uid having nums > 2 ) select ei.tag as tag, count(*) as tag_cnt from tem left join exam_record as er on tem.uid = er.uid left join examination_info as ei on er.exam_id = ei.exam_id group by ei.tag order by tag_cnt desc;