题解 | #未完成率较高的50%用户近三个月答卷情况#

#先选出满足条件的用户 select uid from( select t1.uid ,unfinish_ratio ,row_number() over(order by unfinish_ratio desc) rank_1 ,cnt ,ui.level FROM ( select er.uid, 1-count(submit_time)/count(start_time) as unfinish_ratio from exam_record er inner join user_info ui on er.uid = ui.uid group by er.uid) t1 join (select count(distinct uid) as cnt from user_info) t2 join user_info ui on t1.uid = ui.uid) t3 where rank_1 <= ceil(cnt * 0.5) and level in (6,7)

#筛选每个用户在有试卷做大记录的近三个月中，每个月的答卷数目和完成数目。 select uid ,time_month as start_month ,cnt as total_cnt , finish_cnt as complete_cnt from( select

,row_number() over(partition by uid order by time_month desc) as rank_1 from( select h1.uid ,date_format(start_time,"%Y%m") as time_month ,count(start_time) as cnt ,count(submit_time) as finish_cnt from exam_record inner JOIN (select uid from( select t1.uid ,unfinish_ratio ,row_number() over(order by unfinish_ratio desc) rank_1 ,cnt ,ui.level FROM ( select er.uid, 1-count(submit_time)/count(start_time) as unfinish_ratio from exam_record er inner join user_info ui on er.uid = ui.uid group by er.uid) t1 join (select count(distinct uid) as cnt from user_info) t2 join user_info ui on t1.uid = ui.uid) t3 where rank_1 <= ceil(cnt * 0.5) and level in (6,7)) h1 on exam_record.uid = h1.uid group by uid,date_format(start_time,"%Y%m"))t1 ) t2 where rank_1 <= 3 order by uid asc,time_month asc