Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

01/24/22 22:53:36 (3 years ago)

Author:

193026

Comment:

--

AdvancedReports

@@ -31 +31 @@
 order by num_of_reviews_by_reviewer desc
 }}}
+
+==  Подредување на категориите според средна оценка од сите бизниси кои спаѓаат во нив (во која категорија се најдобро оценетите бизниси), по тромесечие
+
+{{{#!sql
+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
+
+}}}