Q54499152, Oracle - rextester

Language: Layout:

select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
           "Last Year", "LastYear Cap", "LastYear BLCK"
      from
        (
        select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
           lag("Year") over (order by "Year") as "Last Year", 
           lag("CurrYear Cap") over (order by "Year") "LastYear Cap",
           lag("CurrYear BLCK") over (order by "Year") "LastYear BLCK",
           row_number() over (order by "Year" desc) as rn
          from
            (
            with table1(Year, CRN, CID, Cap) as
            (
             select 201910 ,  14   ,  'ABC1'  , 12 from dual union all 
             select 201910 ,  15   ,  'ABC1'  , 14 from dual union all 
             select 201820 ,  25   ,  'ABC1'  , 15 from dual union all 
             select 201820 ,  26   ,  'ABC1'  , 25 from dual union all 
             select 201810 ,  43   ,  'ABC1'  , 10 from dual union all 
             select 201720 ,  55   ,  'ABC1'  , 11 from dual    
            )  ,
                 table2(Year   ,  CRN  , BLCK) as
            (
             select 201910  ,  14   ,  'A1'   from dual union all
             select 201910  ,  15   ,  'A1'   from dual union all
             select 201820  ,  25   ,  'B2'   from dual union all
             select 201820  ,  26   ,  'B2'   from dual union all
             select 201810  ,  43   ,  'C3'   from dual union all
             select 201720  ,  55   ,  'C4'   from dual union all
             select 201720  ,  95   ,  'F5'   from dual union all
             select 201710  ,  65   ,  'D4'   from dual
            )
            select max(t1.year) as "Year", 
                   max(t1.CID) as "CID", sum(t1.Cap) as "CurrYear Cap", max(t2.blck) as "CurrYear BLCK"           
              from table1 t1
              join table2 t2 on t1.year = t2.year and t1.crn = t2.crn
             group by t1.year  
            ) 
        )
     where rn = 1


absolute service time: 1,39 sec