牛客SQL140——未完成率较高的50%用户近三个月答卷情况

题目描述
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c

思路：请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。

1. 计算SQL未完成率

select r.uid,1-count(r.score)/count(r.start_time) rate
from exam_record as r,examination_info as e 
where e.tag='SQL'
group by uid

---

2.占比

select uid,percent_rank() over (order by rate)
from (
	select r.uid,1-count(r.score)/count(r.start_time) rate
	from exam_record as r,examination_info as e 
	where e.tag='SQL'
	group by uid
	) as t1

---

3.较高的50%用户

select uid
from (
    select uid,percent_rank() over (order by rate) as rate
    from (
        select r.uid,1-count(r.score)/count(r.start_time) rate
        from exam_record as r,examination_info as e 
        where e.tag='SQL'
        group by uid
    ) as t1
) as t2
where rate>=0.5

---

4.6级和7级用户

select uid
from user_info
where (level=6 or level=7)and uid in(
    select uid
    from (
        select uid,percent_rank() over (order by rate) as rate
        from (
            select r.uid,1-count(r.score)/count(r.start_time) rate
            from exam_record as r,examination_info as e 
            where e.tag='SQL'
            group by uid
        ) as t1
    ) as t2
    where rate>=0.5
)

---

5.有试卷作答记录的近三个月

select *,dense_rank() over (partition by uid order by date_format(start_time,"%Y%m") desc)
from exam_record
where uid in (
    select uid
    from user_info
    where (level=6 or level=7)and uid in(
        select uid
        from (
            select uid,percent_rank() over (order by rate) as rate
            from (
                select r.uid,1-count(r.score)/count(r.start_time) rate
                from exam_record as r,examination_info as e 
                where e.tag='SQL'
                group by uid
            ) as t1
        ) as t2
        where rate>=0.5
    )
)

---

6. 近三个月数据

# 近三个月数据
select uid,start_time,count(start_time),count(score)
from (
    select uid,exam_id,date_format(start_time,"%Y%m") as start_time,score,dense_rank() over (partition by uid order by date_format(start_time,"%Y%m") desc) as m
    from exam_record
    where uid in (
        select uid
        from user_info
        where (level=6 or level=7)and uid in(
            select uid
            from (
                select uid,percent_rank() over (order by rate) as rate
                from (
                    select r.uid,1-count(r.score)/count(r.start_time) rate
                    from exam_record as r,examination_info as e 
                    where e.tag='SQL'
                    group by uid
                ) as t1
            ) as t2
            where rate>=0.5
        )
    )
)as t3
where m<=3
group by uid,start_time
order by uid,start_time

# 每个月的答卷数目和完成数目