== Напредни извештаи од базата

* Зa сите студенти извештај за тоа колку пати имаат седено во истиот стан и колку имаат платено вкупно во текот на сите договори за еден стан
{{{
select stud.studentid, appus.ime, appus.prezime, c.cityname, b.buildingaddress, 
apt.apartmentfloorandroomnumber , aa.datesigned,apt.apartmentid,aa.datesigned,aa.dateexpires, 
count(apt.apartmentid) as kolku_pati, 
extract(month from age(aa.dateexpires,aa.datesigned)) * sum(aa.apartmentrent)  as vkupno_plateno  from apartment_agreement as aa
join apartment apt on apt.apartmentid = aa.apartmentid 
join student_signs ss on ss.agreementid = aa.agreementid 
join student stud on stud.studentid = ss.studentid
join appuser appus on appus.userid = stud.studentid 
join building b on apt.buildingid = b.buildingid
join city c on c.cityid = b.cityid 
group by 1,2,3,4,5,6,7,8,9,10
order by stud.studentid
}}}

* Извештај за сите студенти и сите места каде имаат седено порано (доколку имале седено), кој бил газдата, колку вкупно платиле, и периодот на договорот подредено по име на студент и датум.
{{{
select au.ime as student_ime,au.prezime as student_prezime, aa.apartment_rent as rent, aa.apartment_rent * extract(month from age(aa.date_expires,aa.date_signed)) as total_paid,
appu.ime as owner_ime, appu.prezime as owner_prezime, aa.date_signed, aa.date_expires  from student s 
join appuser au on au.userid=s.studentid 
left join student_signs ss on ss.studentid = s.studentid 
left join apartment_agreement aa on aa.apartment_agreementid=ss.agreementid 
join apartment_owner ao on ao.ownerid=aa.ownerid 
join appuser appu on appu.userid = ao.ownerid 
order by au.ime,aa.date_signed
}}}

* Бројот на објави од студенти и сопственици на станови по година посебно избројани
{{{
select 
	extract (year from date_posted) as year,
	COUNT(postid) as number_of_posts_student,
	(
		select 
			
			COUNT(postid) as number_of_posts_owner
			from post p
			join owner_post op on op.owner_postid = p.postid 
			
	)
from post p
join student_post sp on sp.student_postid = p.postid
group by extract (year from date_posted)
}}}
* Објавите на газдите и која е разликата меѓу цената објавена и цената потпишана за станот, и колку вкупно имаат заработено во тој договор.
{{{
select au.ime,au.prezime,p.cityname as city_posted_for,p.price as posted_price, aa.apartment_rent as signed_price, abs(p.price-aa.apartment_rent) as difference, aa.apartment_rent * extract(month from age(aa.date_expires,aa.date_signed)) as total_euros_earned from apartment_agreement as aa
join apartment_owner ao on ao.ownerid=aa.ownerid 
join appuser au on au.userid = ao.ownerid 
join apartment apt on apt.apartmentid = aa.apartmentid 
join owner_post op on op.ownerid  = ao.ownerid  
join post p on p.postid = op.owner_postid 
order by au.ime,difference, total_euros_earned 
}}}

* Извештај за станови во системот кои не се моментално издадени и нивните газди соодветно, и доколку биле издадени порано да се покаже кој седел во нив
{{{
select distinct apart.apartmentid,apart.adresa as adresa, apart.ime, apart.prezime, apart.phone_number, au.ime,au.prezime,aa2.date_signed,aa2.date_expires from student s 
join student_signs ss on ss.studentid = s.studentid 
join appuser au on au.userid = s.studentid 
join apartment_agreement aa2 on aa2.apartment_agreementid = ss.agreementid 
right join 
(
select distinct apt.apartmentid,concat(b.cityname,', ',b.building_adress,' ',apt.apartment_adress) as adresa, a.ime, a.prezime, a.phone_number from apartment as apt
left join apartment_agreement aa on aa.apartmentid = apt.apartmentid 
join apartment_owner ao on ao.ownerid = apt.ownerid
join appuser as a on ao.ownerid = a.userid
join building b on apt.buildingid = b.buildingid
) apart on apart.apartmentid=aa2.apartmentid 
where now()>aa2.date_expires or aa2.date_expires isnull 
}}}

* Преференца на студентот за број на цимери според неговите објави
{{{
select  au.userid,au.ime, au.prezime,p.number_of_roommates,count(p.number_of_roommates) as kolku_pati from post as p
join student_post sp on sp.student_postid = p.postid 
join student stud on stud.studentid = sp.studentid 
join appuser au on stud.studentid = au.userid 
group by 1,2,3,4
order by au.userid, kolku_pati DESC
}}}

* Број на објави од сопственици на стан за секој месец
{{{
select extract(month from date_posted)||'/'||extract(year from date_posted) as month_and_year, count(extract(month from date_posted)) from post p
join owner_post op on p.postid=op.owner_postid 
group by date_posted
}}}