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

* za odreden student kolku pati ima sedeno vo sekoj od stanovite kade ima sedeno i kolku vkupno ima plateno vo tekot na site dogovori

{{{
select stud.studentid, appus.ime, appus.prezime, b.cityname, b.building_adress, 
apt.apartment_adress, aa.date_signed,apt.apartmentid,aa.date_signed,aa.date_expires, 
count(apt.apartmentid) as kolku_pati, 
extract(month from age(aa.date_expires,aa.date_signed)) * sum(aa.apartment_rent)  as vkupno_plateno  from apartment_agreement as aa
join apartment apt on apt.apartmentid = aa.apartmentid 
join student_signs ss on ss.agreementid = aa.apartment_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 
group by 1,2,3,4,5,6,7,8,9,10
order by stud.studentid
}}}

* objavite na gazdite i koja e razlikata megju cenata objavena i cenata potpisana za stanot
{{{
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 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
}}}

* stanovi vo sistemot koi momentalno ne se izdadeni
{{{
select 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 
where now()>aa.date_expires  or aa.apartment_agreementid isnull
}}}

* preferenca za roomates na studentot spored objavite
{{{
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
}}}

* broj na postovi od owner vo sekoj grad vo minatata godina vo septemvri
{{{
select a.ime, a.prezime, p.cityname, count(op.ownerid) from apartment_owner ao
join appuser as a on ao.ownerid = a.userid
join owner_post op on ao.ownerid = op.ownerid
join post as p on op.owner_postid = p.postid
where extract(year from p.date_posted)=extract(year from now())-1
and extract(month from p.date_posted)=9
group by 1,2,3
}}}