/*
podaj dane 3 uczniow (id nazwisko imie klasa) z kazdej klasy wytypowanych do nagrody dyrektora ktozy spelniaja nastepujace warunki
-srednia min 3.7 jako srednia dokladnosc do 2 miejsc po przecinku z srednich ocen z poszczegulnych przedmiotow zaokraglane do liczb calkowitych
-maja nie wiecej niz 1 uwage krytyczna
-maja nie wiecej niz 7 nieobecnosci
*/
with sred1 as (
select
u.IdUcznia
,o.IdPrzedmiotu
,CONVERT(DECIMAL(7,0), round (AVG(o.Ocena),0) ) AS sred
from Uczniowie u join Oceny o on u.IdUcznia=o.IdUcznia
group by u.IdUcznia,o.IdPrzedmiotu
), sredk as(
select
s.IdUcznia
,CONVERT(DECIMAL(7,2), round (AVG(s.sred),2) ) AS sred
from sred1 as s
group by s.IdUcznia
having round(AVG(s.sred),2)>=3.7
), laureaci as (
select
u.IdUcznia
,u.Imie
,u.Nazwisko
,k.Nazwa
,RANK() over(partition by k.nazwa order by s.sred desc) ranking
,s.sred
from Uczniowie as u join Klasy as k on u.IdKlasy=k.IdKlasy join sredk as s on u.IdUcznia=s.IdUcznia
where u.IdUcznia in (
 select
 U.IdUcznia
 --,count(n.DataNieobecnosci) nieob
 from Uczniowie u left join Nieobecnosci as n on u.IdUcznia=n.IdUcznia
 group by u.IdUcznia
 having count(n.DataNieobecnosci)<=7
)and u.IdUcznia not in (
select
uw.IdUcznia
--,COUNT(uw.IdUcznia) kryt
from Uwagi as uw  join RodzajeUwag as ru on uw.IdRodzajuUwagi=ru.IdRodzajuUwagi
and ru.CzyKrytyczna=1
group by uw.IdUcznia
having COUNT(uw.IdRodzajuUwagi)>=2
)

)
select 
l.IdUcznia
,l.Imie
,l.Nazwisko
,l.Nazwa
,l.sred
from laureaci as l
where ranking in (1,2,3)