题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
/*
试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
--1.sql试卷未完成率较高的50%用户
--未完成率= 未完成试卷数/试卷总数;
--计算出未完成试卷数，试卷总数，运用窗口函数，以未完成率排序降序，得到名次
--满足未完成率较高的50%的用户，所以，满足排名<= 0.5*总用户数
--2.6.7级用户的近三个月作答记录，每个月的答题数目与完成数目
--先找出三个月的做答记录,分别计算出答题数目和完成数目
--运用表连接，找出level>=6的用户数*/
试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
--1.sql试卷未完成率较高的50%用户
--未完成率= 未完成试卷数/试卷总数;
--计算出未完成试卷数，试卷总数，运用窗口函数，以未完成率排序降序，得到名次
--满足未完成率较高的50%的用户，所以，满足排名<= 0.5*总用户数
--2.6.7级用户的近三个月作答记录，每个月的答题数目与完成数目
--先找出三个月的做答记录,分别计算出答题数目和完成数目
--运用表连接，找出level>=6的用户数*/
select
b.uid,d.mon,d.total_cnt,d.com_cnt
from(
select a.uid
from (
select uid,count(*) as total_cnt,sum(if(score is null,1,0)) as incom_cnt
,sum(if(score is null,1,0))/count(*) as in_rate
,count(*)over() as num
,row_number() over(order by (sum(if(score is null,1,0))/count(*)) desc) as in_rate_num
from exam_record
where exam_id in ('9001','9002')
group by uid
)a
join user_info u on a.uid = u.uid
where u.level>= 6 and ceiling(a.num*0.5)>= a.in_rate_num
)b
left join
(select c.uid,c.mon,c.total_cnt,c.com_cnt
from
(
select uid
,date_format(start_time,'%Y%m') as mon
,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)as num
,count(*) as total_cnt
,sum(if(score is not null,1,0)) as com_cnt
from exam_record e
group by 1,2
)c
where c.num<=3
)d
on b.uid= d.uid
order by 1,2
#
# select c.uid,c.mon,c.total_cnt,c.com_cnt
# from
# (
# select uid
# ,date_format(start_time,'%Y%m') as mon
# ,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)as num
# ,count(*) as total_cnt
# ,sum(if(score is not null,1,0)) as com_cnt
# from exam_record e
# # where e.exam_id in('9001','9002')
# group by 1,2
# )c
# where c.num<=3
# # )d
# # on b.uid= d.uid
# and c.uid in
# (
# select f.uid
# from(
# select a.uid
# from (
# select uid
# ,count(*) as total_cnt
# ,sum(if(score is null,1,0)) as incom_cnt
# ,count(uid)over() as num
# ,row_number() over(order by (sum(if(score is null,1,0))/count(*)) desc) as in_rate_num
# from exam_record
# group by uid
# )a
# where ceiling(a.num*0.5) >= a.in_rate_num
# )f
# join user_info u
# on f.uid = u.uid
# where u.level>= 6
# )
# order by 1,2
数据库刷题题解 文章被收录于专栏
数据分析数据库题目练习题解
