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

with uid_incomplete_rate as (
    select uid, (1 - count(submit_time) / count(start_time)) incomplete_rate
    from exam_record
    left join examination_info using (exam_id)
    where tag = 'SQl'
    group by uid
), 
    uid_f_use as (
    select uid
    from (
        select uid, percent_rank() over (order by incomplete_rate) pct_rank
        from uid_incomplete_rate
    ) a 
    where pct_rank >= 0.5
    and uid in (select uid from user_info where level = 6 or level = 7)
),
    data_f_use as (
    select uid, date_format(start_time, '%Y%m') start_month,
        count(start_time) total_cnt, count(submit_time) complete_cnt
    from exam_record
    where uid in (select uid from uid_f_use)
    group by uid, start_month
)
    select uid, start_month, total_cnt, complete_cnt
    from (
        select uid, start_month, total_cnt, complete_cnt,
            row_number()over(partition by uid order by start_month desc) month_rank
        from data_f_use
    ) b 
    where month_rank <= 3
    order by uid, start_month