Changes between Initial Version and Version 1 of Напредни извештаи од базата (SQL и складирани процедури)Assignment

Timestamp:

01/31/24 23:53:26 (5 months ago)

Author:

193158

Comment:

--

Напредни извештаи од базата (SQL и складирани процедури)Assignment

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај кој прикажува кои продукти дадена индустрија имаат најголема разлика во просечната оценка на рецензија споредено помеѓу последните 30 дена и пред тој датум, сортирано по големина===
+
+{{{#!sql
+SELECT 
+  last_month_avg_scores.productdomainname AS productdomainname,
+  all_previous_month_avg_scores.months_avg_score - last_month_avg_scores.months_avg_score AS avg_score_difference
+FROM
+  (
+    SELECT 
+      pdi.productdomainname,
+      AVG(r.reviewscore) AS months_avg_score
+    FROM 
+      reviews AS r
+    JOIN 
+      productdomainindustry AS pdi ON r.productdomainname = pdi.productdomainname
+    WHERE 
+      pdi.industry = 'Specific Industry'
+      AND dateofreview > now() - interval '30 days'
+    GROUP BY 
+      pdi.productdomainname
+  ) AS last_month_avg_scores
+JOIN
+  (
+    SELECT 
+      pdi.productdomainname,
+      AVG(r.reviewscore) AS months_avg_score
+    FROM 
+      reviews AS r
+    JOIN 
+      productdomainindustry AS pdi ON r.productdomainname = pdi.productdomainname
+    WHERE 
+      pdi.industry = 'Specific Industry'
+      AND dateofreview <= now() - interval '30 days'
+    GROUP BY 
+      pdi.productdomainname
+  ) AS all_previous_month_avg_scores ON last_month_avg_scores.productdomainname = all_previous_month_avg_scores.productdomainname
+ORDER BY 
+  avg_score_difference DESC;
+}}}
+
+
+=== Извештај кој за продуктите со мнозинство негативни рецензии, се прикажуваат рецезентите кои се на c-level позиција и кои имаат linkedin профил, заедно со пораките која им sе пратени ===
+
+{{{#!sql
+select 
+    p.domainname,
+    negativereviews,
+    influentialreviewer.firstname,
+    influentialreviewer.lastname,
+    influentialreviewer.emailaddress,
+    influentialreviewer.linkedinprofile,
+    ms.messagetext
+from
+    (select 
+         r.productdomainname, 
+         count(r.id) as negativereviews
+     from 
+         review r
+     where 
+         r.reviewscore = 1
+     or 
+         r.sentiment = 'negative'
+     group by 
+         r.productdomainname
+     having 
+         count(r.id) > (select count(*) / 2 from review where productdomainname = r.productdomainname)
+    ) as subquery1
+
+join 
+    (select 
+         rev.firstname,
+         rev.lastname,
+         rev.emailaddress,
+         sm.socialmedialink as linkedinprofile,
+         r.productdomainname
+     from 
+         reviewer rev
+     join 
+         socialmediaaccounts sm on rev.id = sm.reviewerid
+     join 
+         review r on rev.id = r.reviewerid
+     where 
+         sm.socialmediatype = 'linkedin'
+     and 
+        rev.position in ('CEO','CFO','COO','CTO','CIO','CMO','CCO','CRO','CHRO','CPO','CSO','CLO','CAO','CDO','CISO','CVO','CKO','CBO','CPEO','CCIO')
+    ) as influentialreviewer on subquery1.productdomainname = influentialreviewer.productdomainname
+left join 
+    messagesequence ms on influentialreviewer.id = ms.recieverId
+join 
+    industry i on p.productdomainname = i.productdomainname
+where 
+    i.industryname = 'specific industry';
+}}}
+