select left(imie,1), imie, len(imie)
from zawodnicy

select imie, wzrost , rank() over (order by wzrost)
from zawodnicy

select imie, wzrost , dense_rank() over (order by wzrost)
from zawodnicy

select imie, wzrost , ntile(3) over (order by wzrost)
from zawodnicy

select imie, wzrost , lag(imie,2) over (order by wzrost)
from zawodnicy

select imie, wzrost , lead(imie,2) over (order by wzrost)
from zawodnicy

select imie, wzrost , max(wzrost) over (order by wzrost)
from zawodnicy

select imie, wzrost , kraj,
 avg(wzrost) over (order by wzrost
	 rows between 2 preceding  and 2 following)
from zawodnicy
 
 select imie, wzrost , kraj,
 count(wzrost) over (partition by kraj order by wzrost
  rows between 2 preceding and unbounded following  )
from zawodnicy

 
 select imie, wzrost , kraj,
 count(wzrost) over (order by wzrost
  rows between unbounded preceding and unbounded following  )
from zawodnicy

  --1) dla kazdego zawodnika wypisz o ile rozni sie jego wzrost od wzrostu 
-- zawodnika od niego nizszego (wzgledem uporzadkowania po wzroscie )

-- rang, dense_rank, lead, lag, ntile, first_value, last_value, 
-- sum, max, avg, count, .. .. 

select imie,wzrost,  wzrost - lag(wzrost,1) over (order by wzrost)
from zawodnicy
order by wzrost

-- 2 ) dla kazdego zawodnika wypisz roznice o ile rozni sie jego wzrost 
  -- od sredniego wzrostu zawodnikow z jego kraju 
  
  select imie, wzrost,kraj,wzrost - avg(convert(decimal, wzrost)) over 
	(partition by kraj order by wzrost  rows between unbounded preceding and unbounded following)
  from zawodnicy


 --3) dla kazdego zawodnika wypisz nazwisko zawodnika od niego wyzszego, z jego kraju 
     -- wzgledem uporzadkowania po wzroscie 

	 select imie,nazwisko, kraj,wzrost, LEAD(nazwisko,1) over 
		(partition by kraj order by wzrost)
	 from zawodnicy


  --4) dla kazdego zawodnika wypisz imie zawodnika najwyzszego z jego kraju, 
     -- podpowiedz: zastosuj funkcje okienkową last_value(imie)
	 
	 select imie, nazwisko, kraj, wzrost, LAST_VALUE(nazwisko) over
		(partition by kraj order by wzrost 
		  rows between current row and unbounded following)
	 from zawodnicy
	 order by kraj,wzrost 

	-- 5) wypisz zawodnikow, ktorych wzrost jest drugi co do wielkosci 	

	select * from 
		(select imie, nazwisko, wzrost , dense_rank() over (order by wzrost desc) r
		from zawodnicy
		) t 
	 where r=2
	 order by wzrost desc
 

-- dla kazdego zawodnika wypisz połowę roku, w której się urodził
	-- (I polowa lub II polowa ) 

	select kraj, p , avg(wzrost)
		from
		(select imie, nazwisko, kraj, data_ur, WZROST,
			iif(month(data_ur) <7,'I polowa','II polowa') p  
		from zawodnicy) t 
	group by kraj, p

	select *
	from
	    (select kraj, WZROST,
			iif(month(data_ur) <7,'I polowa','II polowa') p  
		from zawodnicy) t 
     pivot 
	  (
		avg(wzrost)
		for p in ([I polowa],[II polowa])
	  ) pvt


	  -- zrób zestawienie, które wysietli ile razda z ekip zawodnikow skakala w 
	  -- danym mieście
		-- jako nagłówiki wierszy będą nazwiska zawodników
		-- jako nagłówki kolumn będą nazwy miast
		-- na przecięciu liczba startów 
 
	select * from
		(select kraj, nazwa_miasta, COUNT(*) starty
		from zawodnicy z left join uczestnictwa u on z.id_zawodnika = u.id_zawodnika
				left join zawody w on w.id_zawodow = u.id_zawodow
				left join skocznie s on s.id_skoczni = w.id_skoczni
				left join miasta m on m.id_miasta = s.id_miasta
		group by kraj, nazwa_miasta) q
	pivot 
	(
		sum(starty)
		for nazwa_miasta in (["Lahti"],["Oberstdorf"],["Zakopane"])
	) p