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

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

select
        c.category_id,
        c.category_name,
        avg(r.review_stars) as category_avg_stars
from review r
inner join business b on
        r.business_id = b.business_id
inner join category c on
        b.category_id = c.category_id 
group by c.category_id
order by category_avg_stars desc 

Приказ на бројот на reviews според reviewer, подредени по опаѓачки редослед

 select
        rr.reviewer_name, 
        count(r.review_id) as num_of_reviews_by_reviewer
from
        review r
inner join reviewer rr on
        r.reviewer_id = rr.reviewer_id
group by rr.reviewer_name 
order by num_of_reviews_by_reviewer desc

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

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