问前N名最早在一个月内跨部门打卡的员工

EN

with data as
(
  select 1 as id, 'Dharma' as empname, to_date('26-JAN-2018 08:45', 'DD-MON-YYYY HH24:MI') as emppunchintime, 'Engineering' as empdept from dual union all
  select 2 ,      'Gumpina',           to_date('26-JAN-2018 08:46', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 3 ,      'Kumar',             to_date('26-JAN-2018 08:47', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 4 ,      'Kamal' ,            to_date('26-JAN-2018 08:48', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 5 ,      'Muruges',           to_date('26-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 6 ,      'Gumpina',           to_date('25-JAN-2018 09:01', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 7 ,      'Kumar',             to_date('25-JAN-2018 08:42', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 8 ,      'Biju',              to_date('25-JAN-2018 08:40', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 9 ,      'Divya',             to_date('25-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 10,      'Vali',              to_date('24-JAN-2018 08:52', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual
)
select f.id, f.empname, f.emppunchintime, f.empdept from 
(
  select e.*, row_number() over (partition by e.empdept order by to_char(e.emppunchintime, 'HH24:MI')) as rn2 from
  (
    select d.*, row_number() over (partition by d.empname order by to_char(d.emppunchintime, 'HH24:MI')) as rn from data d
  ) e
  where e.rn = 1
) f
where f.rn2 <= 2
order by f.empdept, f.emppunchintime;

SELECT *
FROM (SELECT *
            ,DENSE_RANK() OVER ( PARTITION BY EmpName, EmpDept ORDER BY EmpJoiningDate) RANK
      FROM EMPLOYEE) EMP
WHERE RANK <= 2