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

• 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