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

思路：统计用户近三个月的答卷数目和完成数目，用户满足条件SQL试卷上未完成率较高的50%用户中，6级和7级用户。

• 近三个月的答卷数目和完成数目
  • 从exam_record统计用户答题时间排名，生成t1：dense_rank()
  • 从t1统计用户的答卷数目和完成数目
    • 分组：group by uid
    • 条件：ranking <=3
• 用户满足的条件,取并集
  • SQL试卷上未完成率较高的50%用户
    • 从exam_record统计用户的未完成率生成in_complete_rate_table:
      • 分组：uid
      • 未完成率：1-count(score)/count(1) as in_complete_rate
      • 条件：SQL试卷
    • 从in_complete_rate_table，对未完成率进行排位 生成in_complete_rate_rank_table：percent_rank() over() as in_complete_rate_rank
    • 条件：in_complete_rate_rank<=0.5
  • 6,7级用户
    • 从user_info筛选uid，where...
• 汇总并排序

注意：是未完成率的排位在50%，不是未完成率的值为50%，使用percent_rank() over()

select 
    uid,
    date_format(start_time,"%Y%m") as start_month,
    count(start_time) as total_cnt,
    count(score) as complete_cnt
from(
    select *,
        dense_rank() over(order by date_format(start_time,"%Y%m") desc) as ranking
    from exam_record
) t1   -- 对答题时间排名
where ranking<=3
and uid in (
    -- 6-7级用户
    select uid from user_info where level=6 or level=7
) 
and uid in (
-- SQL试卷上未完成率较高的50%用户
    select
        uid 
    from(
        select 
            uid,
            percent_rank() over(order by (1-count(score)/count(1))) as in_complete_rate_rank
        from exam_record
        left join examination_info using(exam_id)
        where tag='SQL'
        group by uid    
    ) as in_complete_rate_rank_table
    where in_complete_rate_rank>=0.5
)
group by uid,start_month
order by uid,start_month