SQL查询：筛选学生岗转正式员工的任职记录

现有存储员工任职数据的业务表，需编写SQL查询，筛选出过往任职为学生类岗位、后续转为Employee的员工，无论员工最终状态为ACTIVE/INACTIVE均需纳入统计。

原始表样例数据 #

emp_number   emp_status         eff_start           eff_end             job             Location            
99           ACTIVE             01-JAN-2020         03-MAR-2020        Student          Toronto             
99           ACTIVE             04-MAR-2020         15-AUG-2020        Student          Vancouver           
99           ACTIVE             16-AUG-2020         22-AUG-2020        Contractor       Toronto             
99           ACTIVE             23-AUG-2020         28-SEP-2020        Employee         Toronto             
99           INACTIVE           29-SEP-2020         31-DEC-4712        ex- EMP          Toronto         

10          ACTIVE              03-FEB-2021         06-AUG-2021       Part-Student      India
10          ACTIVE              07-AUG-2021         28-MAY-2022       Part-Student      Toronto
10          ACTIVE              29-MAY-2022         31-DEC-4712       Employee          Toronto

12          ACTIVE              03-FEB-2021         06-AUG-2021       Student           India
12          ACTIVE              07-AUG-2021         28-MAY-2022       Student           Toronto
12          ACTIVE              29-MAY-2022         31-DEC-4712       Contractor        Toronto 

期望返回结果格式 #

emp_number     Previos_Location         Previous_job            Current_eff_start            
99              Vancouver                Student                 29-sep-2020    
10              Toronto                  Part-Student            29-MAY-2022

结果匹配规则 #

• 只要过往岗位名称包含Student字样即符合前置岗位要求
• Previous_Location、Previous_job需取员工任职学生类岗位阶段最新一条记录对应的地点、岗位值
• Current_eff_start需取该员工最新一条任职记录的eff_start日期值
• 若学生类岗位后转为Contractor等非Employee类型岗位，则该员工不纳入结果集

内容的提问来源于stack exchange，提问作者SSA_Tech124