0

Please login or register to vote for this query.

(click on this box to dismiss)

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 QuestionsInQuarter,
       pq.Rate as QuestionsInPreviousQuarter,
       yq.Rate as QuestionsInYearAgoQuarter,
       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()), 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 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()) - 3, 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 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()) - 12, 0)
and Posts.CreationDate > DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 15, 0)
group by TagName)
as yq ON qq.TagName = yq.TagName
order by qq.rate desc
;

view current version

edited feb 14 19

created feb 14 19 gkeyston