1.Извештај за првите 3 валидни прашања кои имаат барем два одговори од професор, од тие три се бира прашањето кое има најмногу одговори во последните 4 месеци

select q3.maximum, p3.* 
from (
	select max(q2.count) as maximum, q2.pra_id
	from(
		select distinct q.count, q.pra_id as pra_id
		from(
			select count(distinct o.o_id), p.pra_id as pra_id from prasanja p 
			join odgovori o on p.pra_id = o.pra_id
			where o.p_id is not null and
			p.pra_datum between now()-interval '4 months' and now()
			group by p.pra_id

		) as q
		join prasanja p on q.pra_id = p.pra_id
		join odgovori o on o.pra_id = p.pra_id 
		where q.count >=2 and o.o_validen is true
	) as q2
	join prasanja p2 on p2.pra_id = q2.pra_id
	group by q2.pra_id 
	order by maximum desc 
	limit 1
) as q3
join prasanja p3 on q3.pra_id = p3.pra_id

2. Извештај за првите три прашања со најмногу одговори од студенти и професори посебно, потоа од тие прашања ја пресметуваме вкупната сума на реакции на одговорите(заедно од професори и студенти) и ги филтритаме само оние кои имаат просечен или надпросечен број на реакции

create view broj_reakcii_na_izbrani_odgovori as (
select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
	select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
		select *  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			where o.s_id is not null
			group by p.pra_id
			order by count(o.o_id) desc
			limit 3 
		) as q1
		union 
		select q2.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			where o.p_id is not null
			group by p.pra_id
			order by count(o.o_id) desc
			limit 3 
		) as q2 
	) as q3
	join prasanja p2 on q3.prasanje = p2.pra_id 
	join odgovori o on o.pra_id =p2.pra_id
	join dava_reakcija dr on o.o_id = dr.o_id
	group by o.o_id, o.pra_id
	union
	select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
		select *  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			where o.s_id is not null
			group by p.pra_id
			order by count(o.o_id) desc
			limit 3 
		) as q1
		union 
		select q2.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			where o.p_id is not null
			group by p.pra_id
			order by count(o.o_id) desc
			limit 3 
		) as q2 
	) as q3
	join prasanja p2 on q3.prasanje = p2.pra_id 
	join odgovori o on o.pra_id =p2.pra_id
	join reagira_na rn on o.o_id = rn.o_id
	group by o.o_id, o.pra_id
) as q4
group by q4.o_id , q4.pra_id
);


create view broj_reakcii_na_odgovor as (
select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
	select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
		select q1.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			group by p.pra_id
 		) as q1
		union 
		select q2.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			group by p.pra_id
		) as q2 
	) as q3
	join prasanja p2 on q3.prasanje = p2.pra_id 
	join odgovori o on o.pra_id =p2.pra_id
	join dava_reakcija dr on o.o_id = dr.o_id
	group by o.o_id, o.pra_id
	union
	select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
		select q1.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			group by p.pra_id
		) as q1
		union 
		select q2.prasanje  from (
			select p.pra_id as prasanje from prasanja p 
			join odgovori o on o.pra_id =p .pra_id
			group by p.pra_id
		) as q2 
	) as q3
	join prasanja p2 on q3.prasanje = p2.pra_id 
	join odgovori o on o.pra_id =p2.pra_id
	join reagira_na rn on o.o_id = rn.o_id
	group by o.o_id, o.pra_id
) as q4
group by q4.o_id , q4.pra_id
);

select brnio.br_reakcii_total, p.* from broj_reakcii_na_izbrani_odgovori brnio
join prasanja p on brnio.pra_id  = p.pra_id 
where brnio.br_reakcii_total >= (select avg(brno.br_reakcii_total) from broj_reakcii_na_odgovor brno)

3.Извештај за пронаоѓање на најдобрите професори т.е. професорите кои имаат одговори со најдобри рејтингзи, имаат поставено најмногу материјали и предаваат најмногу курсеви.

select parts.p_korisnicko_ime korisnicko_ime, parts.rejt+parts.mat+parts.kurs as vk from
(
	select prof.p_korisnicko_ime, case when top_odg.num_rejting is null then 0 else top_odg.num_rejting end as rejt,
								  case when najm_mat.num_mat is null then 0 else najm_mat.num_mat end as mat,
								  case when najm_kurs.num_kurs is null then 0 else najm_kurs.num_kurs end as kurs
	from profesori prof
	left join
	(
		select o.p_id, q1.c1+q2.c2 as num_rejting  from odgovori o 
		join
		(
			select o.o_id, count(o.o_id) c1 from odgovori o 
			left join dava_reakcija dr on o.o_id = dr.o_id 
			where o.p_id is not null and dr.reakcija is true
			group by  o.o_id 
		) q1 on o.o_id = q1.o_id
		join
		(
			select o.o_id,  count(o.o_id) c2 from odgovori o 
			left join reagira_na rn on o.o_id = rn.o_id 
			where o.p_id is not null and rn.reakcija is true
			group by o.o_id 
		) q2 on o.o_id = q2.o_id
		order by num_rejting desc
	) top_odg on prof.p_id = top_odg.p_id
	left join
	(
		select p.p_id, count(m.m_id) num_mat from materijali m 
		join profesori p on m.p_id = p.p_id 
		group by p.p_id
	) najm_mat on prof.p_id =najm_mat.p_id
	left join 
	(
		select p.p_id, count(epn.p_id) num_kurs from kursevi ku 
		join e_prof_na epn on ku.ku_id=epn.ku_id  
		join profesori p on epn.p_id =p.p_id 
		group by p.p_id
	) najm_kurs on prof.p_id = najm_kurs.p_id
) as parts
order by vk desc 

4. Извештај за секој предмет најпопуларните 3 курсеви во последните 3 месеци кои имаат барем по 2 исти категории за материјали и прашања

select pr.pr_id, pr.pr_ime, q1.ku_ime, q1.br_isti_kategorii, count(distinct q2.pra_id) as br_prasanja from predmeti pr
join kursevi ku on pr.pr_id = ku.pr_id 
join 
(
	select ku.ku_id, ku.ku_ime, count(*) br_isti_kategorii from kursevi ku
	left join prasanja pra on ku.ku_id  = pra.ku_id 
	left join e_oznaceno_so eos on pra.pra_id = eos.pra_id 
	left join materijali m on ku.ku_id = m.ku_id 
	where
		eos.ka_id = m.ka_id
	group by ku.ku_id, ku.ku_ime 
	having count(*)>=2
	order by br_isti_kategorii desc
	limit 3
	
) q1 on ku.ku_id = q1.ku_id
join
(
	select pras.ku_id as ku_id, pras.pra_id from prasanja pras
	where 
		now()>=pras.pra_datum and
		age(now(),pras.pra_datum)<= interval'3 months'
) q2 on ku.ku_id=q2.ku_id
group by pr.pr_id, pr.pr_ime,q1.ku_ime, q1.br_isti_kategorii
order by br_prasanja desc