【牛客】SQL140 未完成率较高的50%用户近三个月答卷情况-窗口函数

03-11 7281阅读 0评论

描述

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

（图片来源网络，侵删）

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	3200	7	算法	2020-01-01 10:00:00
2	1002	牛客2号	2500	6	算法	2020-01-01 10:00:00
3	1003	牛客3号♂	2200	5	算法	2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	hard	80	2020-01-01 10:00:00
3	9003	算法	hard	80	2020-01-01 10:00:00
4	9004	PYTHON	medium	70	2020-01-01 10:00:00

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-01 09:01:01	2020-01-01 09:21:59	90
15	1002	9001	2020-01-01 18:01:01	2020-01-01 18:59:02	90
13	1001	9001	2020-01-02 10:01:01	2020-01-02 10:31:01	89
2	1002	9001	2020-01-20 10:01:01
3	1002	9001	2020-02-01 12:11:01
5	1001	9001	2020-03-01 12:01:01
6	1002	9001	2020-03-01 12:01:01	2020-03-01 12:41:01	90
4	1003	9001	2020-03-01 19:01:01
7	1002	9001	2020-05-02 19:01:01	2020-05-02 19:32:00	90
14	1001	9002	2020-01-01 12:11:01
8	1001	9002	2020-01-02 19:01:01	2020-01-02 19:59:01	69
9	1001	9002	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1002	9002	2020-02-02 12:01:01
11	1002	9002	2020-02-02 12:01:01	2020-02-02 12:43:01	81
12	1002	9002	2020-03-02 12:11:01
17	1001	9002	2020-05-05 18:01:01
16	1002	9003	2020-05-06 12:01:01

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

由示例数据结果输出如下：

uid	start_month	total_cnt	complete_cnt
1002	202002	3	1
1002	202003	2	1
1002	202005	2	1

解释：各个用户对SQL试卷的未完成数、作答总数、未完成率如下：

（图片来源网络，侵删）

uid	incomplete_cnt	total_cnt	incomplete_rate
1001	3	7	0.4286
1002	4	8	0.5000
1003	1	1	1.0000

1001、1002、1003分别排在1.0、0.5、0.0的位置，因此较高的50%用户（排位=0.5 and level>=6 ) select uid,start_month,total_cnt,complete_cnt from (select uid,date_format(start_time,'%Y%m') as start_month, count(start_time) as total_cnt, sum(if(submit_time is null,0,1)) as complete_cnt, dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) as rnk2 from exam_record where uid in (select uid from cte) group by uid,date_format(start_time,'%Y%m'))t where rnk2=0.5 and level>=6 ) select uid, replace(act_time, '-', '') as start_month, total_cnt,complete_cnt from (select uid,left(start_time,7) as act_time, count(start_time) as total_cnt, sum(if(submit_time is null,0,1)) as complete_cnt, dense_rank() over (partition by uid order by date_format (start_time,'%Y%m') desc) as rnk2 from exam_record where uid in (select uid from cte) group by uid,left(start_time,7))t where rnk2

相关阅读：

1、IK分词器和ElasticSearch集成使用

2、网络空间搜索引擎- FOFA的使用技巧总结

3、睿智的目标检测66——Pytorch搭建YoloV8目标检测平台

4、YOLOv8目标检测中数据集各部分的作用

5、目标检测任务中常用的数据集格式(voc、coco、yolo)

免责声明

1、本网站属于个人的非赢利性网站，转载的文章遵循原作者的版权声明。
2、本网站转载文章仅为传播更多信息之目的，凡在本网站出现的信息，均仅供参考。本网站将尽力确保所
提供信息的准确性及可靠性，但不保证信息的正确性和完整性，且不对因信息的不正确或遗漏导致的任何
损失或损害承担责任。
3、任何透过本网站网页而链接及得到的资讯、产品及服务，本网站概不负责，亦不负任何法律责任。
4、本网站所刊发、转载的文章，其版权均归原作者所有，如其他媒体、网站或个人从本网下载使用，请在
转载有关文章时务必尊重该文章的著作权，保留本网注明的“稿件来源”，并白负版权等法律责任。

手机扫描二维码访问