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

• Зa сите студенти извештај за тоа колку пати имаат седено во истиот стан и колку имаат платено вкупно во текот на сите договори за еден стан

  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
  

• Извештај за станови во системот кои не се моментално издадени

  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
  

• Преференца на студентот за број на цимери според неговите објави

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