【SQL問題01】現在の部署に所属する社員の一覧

http://qiita.com/jnchito/items/29e22cc5a73da29f65a3
【SQL腕試し問題】現在の部署に所属する社員の一覧を取得するSQL
をOracle11gR2で解きました。

with employees(id,name) as(
select 1,'John' from dual union
select 2,'Mary' from dual union
select 3,'Tom'  from dual),
section_histories(employee_id,start_date,section_name) as(
select 1,date '2013-01-01','Sales' from dual union
select 2,date '2013-01-01','IT'    from dual union
select 3,date '2013-01-01','IT'    from dual union
select 1,date '2014-01-01','IT'    from dual union
select 2,date '2014-01-01','Sales' from dual),
tmp as(
select employee_id,start_date,section_name,
max(start_date) over(partition by employee_id) as MaxStartDate
  from section_histories)
select a.id,a.name,b.section_name,b.start_date
  from employees a
  Join tmp b
    on a.id = b.employee_id
 where b.start_date = b.MaxStartDate
   and b.section_name = 'IT'
order by a.id;

ID  NAME  SECTION_NAME  START_DATE
--  ----  ------------  ----------
 1  John  IT            2014-01-01
 3  Tom   IT            2013-01-01