= Напредни извештаи од базата (SQL и складирани процедури)

== Прикажување на најбараната категорија од страна на членовите во последните три месеци (месец по месец)
{{{#!sql
select mesectri.ime_kategorija, mesectri.pozajmeni_za_prethodni_tri_meseci,
mesecdva.ime_kategorija, mesecdva.pozajmeni_za_prethodni_dva_meseci,
meseceden.ime_kategorija, meseceden.pozajmeni_za_prethoden_mesec 
from 
(
		select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethoden_mesec
		from pozajmica as p
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		join e_od as e on k.id_na_kniga = e.id_knigi
		join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
		where p.datum_na_zemanje between now() - interval'1 months' and now() 
		group by 1
		order by pozajmeni_za_prethoden_mesec desc 
) as meseceden
join 
(
		select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethodni_dva_meseci
		from pozajmica as p
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		join e_od as e on k.id_na_kniga = e.id_knigi
		join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
		where p.datum_na_zemanje between now() - interval'2 months' and now() - interval'1 month' 
		group by 1
		order by pozajmeni_za_prethodni_dva_meseci desc 
) as mesecdva on meseceden.ime_kategorija = mesecdva.ime_kategorija
join 
(
		select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethodni_tri_meseci
		from pozajmica as p
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		join e_od as e on k.id_na_kniga = e.id_knigi
		join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
		where p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months' 
		group by 1
		order by pozajmeni_za_prethodni_tri_meseci desc 
) as mesectri on mesecdva.ime_kategorija = mesectri.ime_kategorija;
}}}

== Прикажување на членот што има прочитано највеќе книги во последните три месеци (месец по месец)
{{{#!sql
select mesectri.ime, mesectri.prezime,  mesecTri.pozajmuvanja_pomegju_2_3_meseci_nanazad,meseceden.ime, meseceden.prezime, meseceden.pozajmuvanja_za_prethodniot_mesec,
mesecdva.ime, mesecdva.prezime, mesecdva.pozajmuvanja_pomegju_1_2_meseci_nanazad
from
(
		select ch.ime,ch.prezime,count(ch.ime) as pozajmuvanja_za_prethodniot_mesec
		from pozajmica as p
		join chlen as chl on p.id_chovek_prave = chl.id_chovek
		join chovek as ch on chl.id_chovek = ch.id_chovek
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		where p.datum_na_zemanje between now() - interval'1 month' and now()
		group by 1,2
		order by pozajmuvanja_za_prethodniot_mesec desc
) as meseceden
join
(
		select ch.ime,ch.prezime,(count(ch.id_chovek)) as pozajmuvanja_pomegju_1_2_meseci_nanazad
		from pozajmica as p
		join chlen as chl on p.id_chovek_prave = chl.id_chovek
		join chovek as ch on chl.id_chovek = ch.id_chovek
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		where p.datum_na_zemanje between now() - interval'2 months' and now() - interval '1 month'
		group by 1,2
		order by pozajmuvanja_pomegju_1_2_meseci_nanazad desc
) as mesecdva on meseceden.ime = mesecdva.ime
join 
(
		select ch.ime,ch.prezime,(count(ch.id_chovek)) as pozajmuvanja_pomegju_2_3_meseci_nanazad
		from pozajmica as p
		join chlen as chl on p.id_chovek_prave = chl.id_chovek
		join chovek as ch on chl.id_chovek = ch.id_chovek
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		where p.datum_na_zemanje between now() - interval'3 months' and now() - interval '2 months'
		group by 1,2
		order by pozajmuvanja_pomegju_2_3_meseci_nanazad desc
) as mesectri on mesecdva.ime = mesectri.ime


}}}

== Прикажување колку позајмици има направено одредена возрасна група корисници во последните 3 месеци (месец по месец)
{{{#!sql
select count(p.id_na_pozajmica) as meseceden,
(
		select count(p.id_na_pozajmica) as pozajmici
		
		from pozajmica as p
		
		join chlen as chl on p.id_chovek_prave = chl.id_chovek
		join chovek as ch on chl.id_chovek = ch.id_chovek
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		
		where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
		else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
		and p.datum_na_zemanje between now() - interval'2 months' and now() - interval '1 month'

) as mesecdva,
(
		select count(p.id_na_pozajmica) as pozajmici
		
		from pozajmica as p
		
		join chlen as chl on p.id_chovek_prave = chl.id_chovek
		join chovek as ch on chl.id_chovek = ch.id_chovek
		join knigi as k on p.id_na_kniga = k.id_na_kniga
		
		where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
		else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
		and p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months'

) as mesectri
from pozajmica as p

join chlen as chl on p.id_chovek_prave = chl.id_chovek
join chovek as ch on chl.id_chovek = ch.id_chovek
join knigi as k on p.id_na_kniga = k.id_na_kniga

where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
and p.datum_na_zemanje between now() - interval'1 month' and now()
}}}

== Прикажување на најдобро оценетата книга 
{{{#!sql
select k.naslov, avg(ocena) as prosecna_ocena from ocena as o

join knigi as k on o.ocena_id_kniga = k.id_na_kniga
join pozajmica as p on k.id_na_kniga = p.id_na_kniga

group by 1
order by prosecna_ocena desc
}}}

== Прикажување на најчитаната книга на еден автор во последните три месеци (месец по месец)
{{{#!sql
select mesectri.naslov, mesectri.ime,mesectri.pozajmuvanja_pomegju_2_3_meseci_nanazad,
mesecdva.naslov, mesecdva.ime,mesecdva.pozajmuvanja_pomegju_1_2_meseci_nanazad, meseceden.naslov, meseceden.ime,meseceden.pozajmuvanja_za_posledniot_mesec
 from
(
		select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_za_posledniot_mesec from
		pozajmica as p
		
		join knigi as k on p.id_na_kniga=k.id_na_kniga
		join izdava as i on k.id_na_kniga = i.id_knigi
		join avtor as a on i.id_avtor = a.id_na_avtor
		where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'1 month' and now()
		
		group by 1,2
		order by pozajmuvanja_za_posledniot_mesec desc
				fetch first row only

) as meseceden
join
(
		select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_pomegju_1_2_meseci_nanazad from
		pozajmica as p
		
		join knigi as k on p.id_na_kniga=k.id_na_kniga
		join izdava as i on k.id_na_kniga = i.id_knigi
		join avtor as a on i.id_avtor = a.id_na_avtor
		where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'2 months' and now() - interval'1month'
		
		group by 1,2
		order by pozajmuvanja_pomegju_1_2_meseci_nanazad desc
				fetch first row only

) as mesecdva on meseceden.ime = mesecdva.ime
join
(
		select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_pomegju_2_3_meseci_nanazad from
		pozajmica as p
		
		join knigi as k on p.id_na_kniga=k.id_na_kniga
		join izdava as i on k.id_na_kniga = i.id_knigi
		join avtor as a on i.id_avtor = a.id_na_avtor
		where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months'
		
		group by 1,2
		order by pozajmuvanja_pomegju_2_3_meseci_nanazad desc
		fetch first row only
) as mesectri on mesecdva.ime = mesectri.ime
}}}

== Прикажување колку често корисниците се најавувале во даден временски интервал
{{{#!sql
select mesectri.ime, mesectri.prezime, meseceden.najavuvanja_vo_prethoden_mesec,
mesecdva.najavuvanja_izmegju_1_2_meseci_nanazad, mesectri.najavuvanja_izmegju_2_3_meseci_nanazad
from 
(
		select ch.ime,ch.prezime,(count(a.log_id)) as najavuvanja_vo_prethoden_mesec
		from authentication as a
		
		join chovek as ch on a.id_chovek = ch.id_chovek
		where a.timestamp_log between now() - interval '1 month' and now()
		
		group by 1,2
		order by najavuvanja_vo_prethoden_mesec desc
) as meseceden
join
(
		select ch.ime,ch.prezime,(count(a.log_id)) as najavuvanja_izmegju_1_2_meseci_nanazad
		from authentication as a
		
		join chovek as ch on a.id_chovek = ch.id_chovek
		where a.timestamp_log between now() - interval '2 months' and now() - interval '1 month'
		
		group by 1,2
		order by najavuvanja_izmegju_1_2_meseci_nanazad desc
) as mesecdva on meseceden.ime = mesecdva.ime and meseceden.prezime = mesecdva.prezime
join
(
		select ch.ime,ch.prezime,case when count(a.log_id) is null then 0 else count(a.log_id) end as najavuvanja_izmegju_2_3_meseci_nanazad
		from authentication as a
		
		join chovek as ch on a.id_chovek = ch.id_chovek
		where a.timestamp_log between now() - interval '3 months' and now() - interval '2 months'
		
		group by 1,2
		order by najavuvanja_izmegju_2_3_meseci_nanazad desc 
}}}