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

# 基础表
with table1 as(
    select uid , level, tag,
    date_format(start_time,"%Y%m") as start_month,
    date_format(submit_time,"%Y%m") as submit_month
    from exam_record left join examination_info using(exam_id)
    left join user_info using(uid)
),

# 统计SQL试卷上未完成率
# 6级和7级用户在有试卷作答记录
table2 as(
    select uid, 
    ((count(start_month) - count(submit_month)) / count(start_month)) as incomplete_rate
    from table1
    where tag = 'SQL'
    group by uid
),

# （目标用户id）统计SQL试卷上未完成率较高的50%用户中
table3 as(
    SELECT uid, incomplete_rate , 
    percent_rank()over(order by incomplete_rate) as rate_rank#不用分组partition by 因为上一轮已经分组
    FROM table2
),

# select uid,start_month
# from table1
# # 输出6级和7级 近三个有作答记录的月  的 作答数、完成数
table4 as(
    select uid, start_month , 
    count(start_month) as total_cnt ,
    count(submit_month) as complete_cnt,
    dense_rank()over(partition by uid order by start_month desc) as rank_month
    from table1
    where (level = 6 or level = 7) and uid in (select uid from table3 where rate_rank >= 0.5)
    group by uid, start_month
)
select uid,start_month,total_cnt,complete_cnt
from table4
where rank_month <= 3
order by uid , start_month