질문이 좀 매끄럽지 않고 모호한 부분이 많은데요.
 - 구분자의 의미와 N 에서 Y 로 변경되는 의미가?
 - 최신날짜로 변경된 사람들을 뽑겠다는 말의 정확한 의미가?

with emp (nm, title) as (
select '파랑', '사원' from dual union all 
select '노랑', '대리' from dual union all 
select '주황', '대리' from dual union all 
select '분홍', '과장' from dual),
emp_up (nm, dt, title) as ( 
select '파랑','2015-03-03','주임' from dual union all
select '노랑','2015-03-03','과장' from dual union all 
select '파랑','2016-03-25','대리' from dual union all 
select '노랑','2016-03-25','차장' from dual)
select nm,dt
from (select a.nm, a.title,b.dt,row_number() over(partition by a.nm order by dt desc) rn
        from emp a, emp_up b
        where a.nm = b.nm
          and a.title <> b.title)
where rn = 1;

SELECT nm
     , MAX(dt) dt
  FROM emp_up
 GROUP BY nm
;