Stack Overflow tag popularity during most recent quarter, previous quarter and year-ago quarter

0

Please login or register to vote for this query.

(click on this box to dismiss)

Robotics Meta

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

-- Popularity of StackOverflow cloud platform tags during most recent month and last 12 months

select 
       qq.TagName as Tag,
       row_number() over (order by qq.Rate desc) as QuarterRank,
       row_number() over (order by pq.Rate desc) as PreviousQuarterRank,
       row_number() over (order by yq.Rate desc) as YearAgoQuarterRank,
       qq.Rate as QuarterQuestions,
       pq.Rate as PreviousQuarterQuestions,
       yq.Rate as YearAgoQuarterQuestions,
       qq.QuarterViews,
       pq.PreviousQuarterViews,
       yq.YearAgoQuarterViews,
       qq.QuarterComments,
       pq.PreviousQuarterComments,
       yq.YearAgoQuarterComments,
       qq.QuarterAnswers,
       pq.PreviousQuarterAnswers,
       yq.YearAgoQuarterAnswers

from

(select count(PostId) as Rate, TagName, sum(ViewCount) as QuarterViews, sum(CommentCount) as QuarterComments, count(AcceptedAnswerId) as QuarterAnswers
from
  Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId
and Posts.CreationDate < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 27, 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 30, 0)
group by TagName) as qq

INNER JOIN

(select count(PostId) as Rate, TagName, sum(ViewCount) as PreviousQuarterViews, sum(CommentCount) as PreviousQuarterComments, count(AcceptedAnswerId) as PreviousQuarterAnswers
from
  Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId
and Posts.CreationDate < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 30, 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 33, 0)
group by TagName)
as pq ON qq.TagName = pq.TagName

INNER JOIN

(select count(PostId) as Rate, TagName, sum(ViewCount) as YearAgoQuarterViews, sum(CommentCount) as YearAgoQuarterComments, count(AcceptedAnswerId) as YearAgoQuarterAnswers
from
  Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId
and Posts.CreationDate < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 39, 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 42, 0)
group by TagName)
as yq ON qq.TagName = yq.TagName
where (qq.Rate > 50 OR pq.Rate > 50 OR yq.Rate > 50)
order by qq.rate desc
;

Enter Parameters

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