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

• З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.apartmentrent as rent, aa.apartmentrent * extract(month from age(aa.dateexpires,aa.datesigned)) as total_paid,
  appu.ime as owner_ime, appu.prezime as owner_prezime, aa.datesigned, aa.dateexpires  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.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.datesigned
  

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

  select 
  	extract (year from dateposted) 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.ownerpostid = p.postid 
  			
  	)
  from post p
  join student_post sp on sp.studentpostid = p.postid
  group by extract (year from dateposted)
  

• Објавите на газдите и која е разликата меѓу цената објавена и цената потпишана за станот, и колку вкупно имаат заработено во тој договор.

  select au.ime,au.prezime,c.cityname as city_posted_for,p.price as posted_price, aa.apartmentrent as signed_price, abs(p.price-aa.apartmentrent) as difference, aa.apartmentrent * extract(month from age(aa.dateexpires,aa.datesigned)) 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.ownerpostid 
  join city c on c.cityid = p.cityid 
  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.datesigned,aa2.dateexpires 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.agreementid = ss.agreementid 
  right join 
  (
  select distinct apt.apartmentid,concat(c.cityname,', ',b.buildingaddress,' ',apt.apartmentfloorandroomnumber) 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
  join city c on c.cityid = b.cityid 
  ) apart on apart.apartmentid=aa2.apartmentid 
  where now()>aa2.dateexpires or aa2.dateexpires isnull 
  

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

  select  au.userid,au.ime, au.prezime,p.numberofroommates,count(p.numberofroommates) as kolku_pati from post as p
  join student_post sp on sp.studentpostid = 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 dateposted)||'/'||extract(year from dateposted) as month_and_year, count(extract(month from dateposted)) from post p
  join owner_post op on p.postid=op.ownerpostid 
  group by dateposted