--Prikazati proseke ocena studenata i razliku do proseka prvog narednog i prethodnog studenta (sortiranog po proskeu ocena)
with proseci(indeks, ime, prezime, prosek)
as
(
 select a.indeks, ime, prezime, dec(avg(ocena*1.0),4,2)
 from   ispit a, dosije b 
 where  ocena >5
 and    a.indeks=b.indeks
 and    a.datum_ispita=(select max(datum_ispita)
                        from   ispit c
                        where  a.indeks=c.indeks
                        and    a.id_predmeta=c.id_predmeta 
                       )
 group by a.indeks,ime,prezime
)
select indeks,ime, prezime, prosek as "Prosek", 
       lead(prosek,1) over(order by prosek desc) as "Prvi naredni prosek",
       lag (prosek,1) over(order by prosek desc) as "Prvi prethodni prosek",
       lead(prosek,1) over(order by prosek desc) -prosek as "Razlika do narednog proseka",
       lag (prosek,1) over(order by prosek desc) -prosek as "Razlika do prethodnog proseka"        
from   proseci
order by prosek desc;

