【阿里巴巴】筛选各类试卷突出员工
WHERE 条件JOIN 连接GROUP BY窗口函数日期函数ORDER BY标签画像面试真题
题目描述
来源：阿里巴巴 SQL 面试真题
某公司组织员工年终考核。对于每类试卷，作答用时严格少于该类别平均作答用时，并且个人得分严格高于该类别总体平均分的员工，记为该类别的突出员工。
数据表
emp_info 员工信息表
| 字段 | 类型 | 说明 |
|---|---|---|
| emp_id | BIGINT | 员工 ID，主键 |
| emp_name | VARCHAR | 员工姓名 |
| emp_level | INT | 员工等级，小于 7 为普通员工，其余为领导 |
| register_time | DATETIME | 入职时间 |
examination_info 考核试卷信息表
| 字段 | 类型 | 说明 |
|---|---|---|
| exam_id | BIGINT | 试卷 ID，主键 |
| tag | VARCHAR | 试卷类别 |
| duration | INT | 试卷规定时长，单位分钟 |
| release_time | DATETIME | 发布时间 |
exam_record 试卷作答记录表
| 字段 | 类型 | 说明 |
|---|---|---|
| emp_id | BIGINT | 员工 ID |
| exam_id | BIGINT | 试卷 ID |
| start_time | DATETIME | 开始作答时间 |
| submit_time | DATETIME | 交卷时间 |
| score | DECIMAL | 得分 |
突出员工口径
- 作答用时按
submit_time - start_time计算。 - 平均作答用时和平均分均按试卷类别
tag统计，所有有效作答记录都参与均值计算。 - 突出员工必须同时满足：作答用时严格小于类别平均值，且得分严格大于类别平均值。
- 只输出
emp_level < 7的非领导员工。 - 同一员工在同一类别有多条突出记录时，该类别只输出一次。
题目要求
- 输出员工 ID、员工等级和突出试卷类别。
- 按员工 ID 升序排列。
- 同一员工有多个突出类别时，按对应试卷 ID 升序排列。
期望输出列
| 列名 | 说明 |
|---|---|
| emp_id | 员工 ID |
| emp_level | 员工等级 |
| tag | 突出试卷类别 |
数据样例
| emp_idPKBIGINT | emp_nameVARCHAR(50) | emp_levelINT | register_timeDATETIME |
|---|---|---|---|
| 1 | Alice | 5 | 2020-03-01 09:00:00 |
| 2 | Bob | 6 | 2020-06-15 09:00:00 |
| 3 | Carol | 4 | 2021-01-10 09:00:00 |
| 4 | David | 7 | 2018-05-20 09:00:00 |
| 5 | Eve | 3 | 2021-08-01 09:00:00 |
输入数据显示 5 / 5 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里