题解 | 未完成率top50%用户近三个月答卷情况

用最笨的方法写了个答案= =

# SQL试卷 未完成率>50 6和7级用户 近三个月每个月答卷数目和完成数目
with
t1 as(
    select
    ui.uid,
    (count(er.start_time)-count(er.submit_time)) as incomplete_cnt,
    count(er.start_time) as total_cnt,
    ((count(er.start_time)-count(er.submit_time))/count(er.start_time)) as incomplete_rate,
    percent_rank() over (order by ((count(er.start_time)-count(er.submit_time))/count(er.start_time))) as incomplete_rank

    from
        user_info as ui
        inner join
        exam_record as er using(uid)
        inner join
        examination_info as ei using(exam_id)
    where
        ei.tag='SQL'
    group by
        ui.uid
),
t2 as(
    select
        t1.uid
    from
        t1
        inner join
        user_info using(uid)
    where
        incomplete_rank>=0.5 and user_info.level>=6
),
t3 as(
    select
    t2.uid,
    max(start_time) as now
    from
        t2
        inner join
        exam_record using(uid)
    group by
        t2.uid
),
t4 as(
    select
        t3.uid,
        date_format(start_time,'%Y%m') as start_month,
        submit_time
    from
        t3
        inner join
        exam_record using(uid)
    where
        timestampdiff(day,start_time,t3.now)<=94

)

select
    uid,
    start_month,
    count(start_month) as total_cnt,
    count(submit_time) as complete_cnt
from
    t4
group by
    uid,start_month
order by
    uid,start_month