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

Подредување на категориите според средна оценка од сите бизниси кои спаѓаат во нив (во која категорија се најдобро оценетите бизниси), по тромесечие

drop view if exists best_rated_businesses_by_trimesters cascade;
create view best_rated_businesses_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