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 ( (select count(*) from dava_reakcija as dr where dr.o_id = o.o_id and dr.reakcija = true) -
       (select count(*) from dava_reakcija as dr where dr.o_id = o.o_id and dr.reakcija = false) ) 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 ( (select count(*) from reagira_na as rn where rn.o_id = o.o_id and rn.reakcija = true) -
       (select count(*) from reagira_na as rn where rn.o_id = o.o_id and rn.reakcija = false) ) 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 ( (select count(*) from dava_reakcija as dr where dr.o_id = o.o_id and dr.reakcija = true) -
       (select count(*) from dava_reakcija as dr where dr.o_id = o.o_id and dr.reakcija = false) ) 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 ( (select count(*) from reagira_na as rn where rn.o_id = o.o_id and rn.reakcija = true) -
       (select count(*) from reagira_na as rn where rn.o_id = o.o_id and rn.reakcija = false) ) 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 pras.*, q2.sum from (select distinct q1.pra_id, sum(q1.br_reakcii_total)
                         from (select brnio.br_reakcii_total, p.*
                               from broj_reakcii_na_izbrani_odgovori brnio
                                        join prasanja p on brnio.pra_id = p.pra_id
                                        join odgovori o on brnio.o_id = o.o_id
                               where brnio.br_reakcii_total >=
                                     (select avg(brno.br_reakcii_total) from broj_reakcii_na_odgovor brno)) as q1
                         group by q1.pra_id) as q2
join prasanja as pras on pras.pra_id = q2.pra_id;

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

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 
limit 5

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 

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

select prof.p_ime, prof.p_prezime, q3.najbrzo_vreme
from project.profesori prof
join (
     select q2.p_id, min(q2.avg_vreme) najbrzo_vreme
     from (
         select q1.p_id, avg(q1.vreme_odg) avg_vreme
         from (
             select o.p_id, age(o.o_datum, p.pra_datum) vreme_odg
             from project.odgovori o
             join project.prasanja p on o.pra_id = p.pra_id
             where o.p_id is not null
          ) q1
          group by q1.p_id
      ) q2
      group by q2.p_id
      order by 2
      limit 1
) q3 on prof.p_id = q3.p_id