1

Please login or register to vote for this query.

(click on this box to dismiss)

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'
    and p.id NOT IN (
    SELECT TOP 1 ps.id
    FROM posts ps
    join posttypes pts on ps.posttypeid = pts.id
    join PostTags ptgs on ptgs.PostId = ps.Id
    join Tags ts on ts.Id = ptgs.TagId
    WHERE ts.Id = t.Id
    ORDER BY ps.ViewCount DESC)
    GROUP BY t.TagName having count(p.id)>3
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'
  and p.id NOT IN (
    SELECT TOP 1 ps.id
    FROM posts ps
    join posttypes pts on ps.posttypeid = pts.id
    join PostTags ptgs on ptgs.PostId = ps.Id
    join Tags ts on ts.Id = ptgs.TagId
    WHERE ts.Id = t.Id
    ORDER BY ps.ViewCount DESC)
GROUP BY t.TagName having count(p.id)>3
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'
    and p.id NOT IN (
    SELECT TOP 1 ps.id
    FROM posts ps
    join posttypes pts on ps.posttypeid = pts.id
    join PostTags ptgs on ptgs.PostId = ps.Id
    join Tags ts on ts.Id = ptgs.TagId
    WHERE ts.Id = t.Id
    ORDER BY ps.ViewCount DESC)
GROUP BY t.TagName having count(p.id)>3
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'
      and p.id NOT IN (
    SELECT TOP 1 ps.id
    FROM posts ps
    join posttypes pts on ps.posttypeid = pts.id
    join PostTags ptgs on ptgs.PostId = ps.Id
    join Tags ts on ts.Id = ptgs.TagId
    WHERE ts.Id = t.Id
    ORDER BY ps.ViewCount DESC)
GROUP BY t.TagName having count(p.id)>3
ORDER BY avg(AnswerCount)  DESC

fork query

edited mar 7 14

created mar 7 14 Jonathan Van Matre