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

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

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 по тромесечие

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