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

Извештај кој прикажува кои продукти дадена индустрија имаат најголема разлика во просечната оценка на рецензија споредено помеѓу последните 30 дена и пред тој датум, сортирано по големина

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е пратени

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';

Извештај кој ги прикажува Auditly корисниците кои мониторираат продукти кои имаат помалку просечни месечни рецензии од просекот

select au.id, au.fistname, au.lastname, au.emailaddress
from auditlyuser as au
join review as r 
on au.productdomainname = r.productdomainname
where 
(select count(rr.id)/count(distinct extract(year_month from r.dateofreview)) as monthlyaverage
from review as rr
where r.productdomainname = rr.productdomainname
group by r.productdomainname
) >
(select count(rrr.id)/count(distinct extract(year_month from r.dateofreview)) as monthlyaverage
from review as rrr
where r.productdomainname <> rrr.productdomainname
group r.productdomainname
)