Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

01/25/22 01:07:46 (3 years ago)

Author:

193026

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата
 
-== Подредување на категориите според средна оценка од сите бизниси кои спаѓаат во нив (во која категорија се најдобро оценетите бизниси)
 
-{{{#!sql
-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, подредени по опаѓачки редослед
-
-{{{#!sql
- 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
-}}}
 
 ==  Подредување на категориите според средна оценка од сите бизниси кои спаѓаат во нив (во која категорија се најдобро оценетите бизниси), по тромесечие
@@ -69 +40 @@
 
 }}}
+
+
+== Приказ на бројот на 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
+
+
+
+}}}