/*
	Resenje redovnog kolokvijuma 2015-2016
*/


use socialNetwork


-- 1. zadatak


--I nacin


select max(p.datumObjave)
from Post p join tipPosta tp 
	on p.idTipa = tp.idTipa 
		and tp.tip = 'slika'

-- II nacin


select max(datumObjave)
from post
where idTipa in (
					select idTipa
					from tipPosta
					where tip like 'slika'
				)

-- 2. zadatak


-- I nacin

select k.ime, COUNT(p.idKorisnika2) 
						as brojPrijatelja
from Korisnik k join Prijatelji p 
	on k.id = p.idKorisnika1
group by k.ime, k.id

						
--II nacin

select k.ime , ( 
					select count(*)
					from Prijatelji p
					where k.id = p.idKorisnika1
				) as broj_prijatelja
from Korisnik k

-- 3. zadatak


-- I nacin


select ime
from Korisnik
where id in (
				select distinct idKorisnika1
				from Prijatelji
				where DATEPART(MONTH,getdate()) 
					= DATEPART(month,datum)
			)

-- II nacin


select k.ime
from korisnik k
where exists ( 
				select *
				from prijatelji p
				where p.idkorisnika1=k.id 
					and datepart(month,p.datum)
						= datepart(month,getdate()) 
			  )

-- 4. zadatak


-- I nacin


-- I nacin


--ukupan broj slika po korisniku

create view broj_slika_po_korisniku as
select idVlasnika, count(*) as 'brojSlika'
from post p join tipPosta tp on p.idTipa = tp.idTipa
where tp.tip like 'slika'
group by idVlasnika

--ukupan broj lajkova na svim slikama po korisniku

create view ukupan_broj_lajkova_slika_po_korisniku as
select idVlasnika as 'idVlasnika', 
			count(*) as 'brojLajkova'
from lajk l join post p on l.idPosta = p.id 
	join tipPosta tp on p.idTipa = tp.idTipa
where tp.Tip like 'slika'
group by idVlasnika


select bs1.idVlasnika, brojLajkova*1.0/brojSlika ProsekLajkova
from broj_slika_po_korisniku bs1 
	join ukupan_broj_lajkova_slika_po_korisniku bl1
		on bs1.idVlasnika = bl1.idVlasnika
where brojLajkova*1.0/brojSlika >= 3


-- II naci


/*
	Za svaku postovanu sliku nekog korisnika izracunaj
	koliko je dobio lajkova za nju.

*/

create view LajkoviPoSlici as
select p.idvlasnika, l.idposta,count(*) BrLajkova
from post p join tipposta tp on p.idtipa=tp.idtipa and tp.tip like 'slika'
			join lajk l on l.idposta=p.id
group by p.idvlasnika, l.idposta


/*
	Za svakog korisnika izracunaj koliko ima ukupno lajkova i to podeli sa
	brojem slika, a to je ekvivalentno trazenju srednje vrednosti broja lajkova
	za sve njegove slike.
*/

select idvlasnika,avg(brlajkova * 1.0) 'ProsekLajkova'
from LajkoviPoSlici
group by idvlasnika
having avg(brlajkova * 1.0) > 3




-- 5. zadatak


--jedna osoba spojena sa svim svojim prijateljima i svim prijateljima svojih prijatelja

create view prijateljiPrijatelja as
select 	p1.idKorisnika1 as 'osoba',
		p1.idKorisnika2 as 'prijatelji',
		p2.idKorisnika2 as 'prijatelji prijatelja'
from Prijatelji p1 join Prijatelji p2 
	on p1.idKorisnika2 = p2.idKorisnika1 
		and p1.idKorisnika1 != p2.idKorisnika2


select osoba, count([prijatelji prijatelja]) 
			as 'brojPotencijalnihPrijatelja'
from prijateljiPrijatelja p1 left join prijatelji p 
		on p1.osoba = p.idKorisnika1 
				and p1.[prijatelji prijatelja] = p.idKorisnika2
where p.idKorisnika1 is null
group by osoba


--6. zadatak


create view sest1 as
select p1.idKorisnika1 as 'kor1', p2.idKorisnika1 as 'kor2', count(*) as 'brojZajednickihPrijatelja'
from Prijatelji p1 join Prijatelji p2 on p1.idKorisnika1 != p2.idKorisnika1 
	and p1.idKorisnika2 = p2.idKorisnika2
group by p1.idKorisnika1,p2.idKorisnika1

create view sest2 as
select s.kor1,s.kor2,s.brojZajednickihPrijatelja
from sest1 s left join Prijatelji p 
	on s.kor1 = p.idKorisnika1 
		and s.kor2 = p.idKorisnika2
where p.idKorisnika1 is null

select s2_1.*
from sest2 s2_1 left join sest2 s2_2 on s2_1.kor1 = s2_2.kor1 
	and s2_1.kor2 != s2_2.kor2 
	and s2_1.brojZajednickihPrijatelja < s2_2.brojZajednickihPrijatelja
where s2_2.kor1 is null



--7. zadatak


create view sedmi1 as
select idVlasnika, p.id, count(*) as 'brojLajkovaPoPostu'
from post p join lajk l on p.id = l.idPosta
where DATEDIFF(DAY,p.datumObjave,getdate())<=(365*3+30)
group by idVlasnika, p.id

create view sedmi2 as
select idVlasnika, p.id, count(*) as 'brojLajkovaPoPostu'
from post p join lajk l on p.id = l.idPosta
where DATEDIFF(DAY,p.datumObjave,getdate())>(365*3+30)
group by idVlasnika, p.id

create view sedmi3 as
select idVlasnika, avg(brojLajkovaPoPostu*1.0) as 'prosek'
from sedmi1
group by idVlasnika

create view sedmi4 as
select idVlasnika, avg(brojLajkovaPoPostu*1.0) as 'prosek'
from sedmi2
group by idVlasnika

select case when s3.idVlasnika is null then s4.idVlasnika else s3.idVlasnika end as 'idVlasnika', 
						case when s3.prosek > s4.prosek then 'raste'
						when s3.prosek <= s4.prosek then 'opada'
						when s3.prosek is null then 'opada'	
						when s4.prosek is null then 'raste' end as 'popularnost'
from sedmi3 s3 full outer join sedmi4 s4 on s3.idVlasnika = s4.idVlasnika