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



== Средна оценка од сите бизниси кои спаѓаат во категорија по тромесечие

{{{#!sql
drop view if exists avg_rating_by_category_by_trimesters cascade;
create view avg_rating_by_category_by_trimesters as 


SELECT DISTINCT  cc.category_id,
      			 cc.category_name,



CASE WHEN tabela.quarter=1 THEN tabela.category_avg_stars ELSE 0 END AS first_q,
CASE WHEN tabela.quarter=2 THEN tabela.category_avg_stars ELSE 0 END AS second_q,
CASE WHEN tabela.quarter=3 THEN tabela.category_avg_stars ELSE 0 END AS third_q,
CASE WHEN tabela.quarter=4 THEN tabela.category_avg_stars ELSE 0 END AS fourth_q


from business bb 
join category cc on
	cc.category_id = bb.category_id 
join review rr on
	rr.business_id = bb.business_id 
	
join (
		select distinct c2.category_id, c2.category_name,
		extract(quarter FROM r2.review_timestamp) AS quarter,
        avg(r2.review_stars) as category_avg_stars
  from business b2 
  join category c2 on
  		b2.category_id = c2.category_id 
  join review r2 on
  		r2.business_id = b2.business_id
  group by 1,2,3
) as tabela on cc.category_id = tabela.category_id

}}}


== Приказ на бројот на reviews според reviewer по тромесечие


{{{#!sql
drop view if exists num_of_reviews_by_reviewer_by_trimester cascade;
create view num_of_reviews_by_reviewer_by_trimester as 


SELECT DISTINCT  
				rrr2.reviewer_id,
      			 rrr2.reviewer_name,



CASE WHEN tabela.quarter=1 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS first_q,
CASE WHEN tabela.quarter=2 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS second_q,
CASE WHEN tabela.quarter=3 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS third_q,
CASE WHEN tabela.quarter=4 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS fourth_q


from
       	 	review rrr
	inner join reviewer rrr2 on
        	rrr.reviewer_id = rrr2.reviewer_id
	
join (
	select
        	distinct r2.reviewer_name, 
        	r2.reviewer_id,
        	extract(quarter FROM r.review_timestamp) AS quarter,
        	count(r.review_id) as num_of_reviews_by_reviewer
	from
       	 	review r
	inner join reviewer r2 on
        	r.reviewer_id = r2.reviewer_id
	group by 1,2,3
) as tabela on rrr2.reviewer_id = tabela.reviewer_id



}}}