【SQL問題02】締め切り日が近いイベントの一覧

http://qiita.com/jnchito/items/7d5d7e829690ea3c4d6f
【SQL腕試し問題】締め切り日が近いイベントの一覧の取得するSQL
をOracle11gR2で解きました。

with events(id,name) as(
select 1,'Completed event'   from dual union
select 2,'No schedule event' from dual union
select 3,'Continuing event'  from dual union
select 4,'Future event 1'    from dual union
select 5,'Future event 2'    from dual),
schedules(id,event_id,due_date) as(
select 1,1,date '2014-01-23' from dual union
select 2,3,date '2014-01-01' from dual union
select 3,3,date '2014-02-01' from dual union
select 4,3,date '2014-03-01' from dual union
select 5,4,date '2014-01-24' from dual union
select 6,4,date '2014-01-25' from dual union
select 7,5,date '2014-01-24' from dual),
sysdate_dummy(sys_date) as(
select date '2014-01-24' from dual),
tmp as(
select a.id,a.name,b.ID as schedule_id,b.due_date,
count(b.event_id) over(partition by a.id) as JoinCnt,
min(case when b.due_date >= (select max(sys_date) from sysdate_dummy)
         then b.due_date end)
over(partition by b.event_id) as SaisyouJyoukai
  from events a
  Left Join schedules b
    on a.id = b.event_id)
select id,name,schedule_id,due_date
  from tmp
 where JoinCnt = 0
    or due_date = SaisyouJyoukai
order by due_date,schedule_id;

ID  NAME               SCHEDULE_ID  DUE_DATE
--  -----------------  -----------  ----------
 4  Future event 1               5  2014-01-24
 5  Future event 2               7  2014-01-24
 3  Continuing event             3  2014-02-01
 2  No schedule event         null  null