Trailing 3 Months roll ups

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

select TOP 5 t.TagName, sum(viewcount) as NewQuestionViews
from posts p
join posttypes pt on p.posttypeid = pt.id
join PostTags ptg on ptg.PostId = p.Id
join Tags t on t.Id = ptg.TagId
where creationdate >= dateadd(mm,-3,getdate())
  and pt.name = 'Question'
GROUP BY t.TagName
ORDER BY sum(viewcount) DESC

select TOP 5 t.TagName,  count(p.id) as NewQuestions 
from posts p
join posttypes pt on p.posttypeid = pt.id
join PostTags ptg on ptg.PostId = p.Id
join Tags t on t.Id = ptg.TagId
where creationdate >= dateadd(mm,-3,getdate())
  and pt.name = 'Question'
GROUP BY t.TagName
ORDER BY  count(p.id) DESC 

select TOP 5 t.TagName,  sum(viewcount) / count(p.id) as AvgQuestionViews 
from posts p
join posttypes pt on p.posttypeid = pt.id
join PostTags ptg on ptg.PostId = p.Id
join Tags t on t.Id = ptg.TagId
where creationdate >= dateadd(mm,-3,getdate())
  and pt.name = 'Question'
GROUP BY t.TagName
ORDER BY sum(viewcount) / count(p.id) DESC  

select TOP 5 t.TagName, avg(AnswerCount) as AnswersPerQuestion
from posts p
join posttypes pt on p.posttypeid = pt.id
join PostTags ptg on ptg.PostId = p.Id
join Tags t on t.Id = ptg.TagId
where creationdate >= dateadd(mm,-3,getdate())
    and pt.name = 'Question'
GROUP BY t.TagName
ORDER BY avg(AnswerCount)  DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results