Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games
select TOP 50 RecentCol.Tag, RecentCnt, row_number() over (order by recentCol.recentCnt desc) as RecentRank, TotalCnt, row_number() over (order by totalCol.totalCnt desc) as TotalRank from ( select b.TagName as Tag, count(a.PostId) as recentCnt from (select PostId from Tags, PostTags where Tags.Id = TagId and TagName = 'javascript' ) as a INNER JOIN (select PostId, Tags.TagName from Tags, PostTags, Posts where Tags.Id = TagId and TagName not like '%javascript%' and PostId = Posts.Id and CreationDate > DATEADD(month , -3, GETDATE()) and CreationDate < GETDATE() ) as b on a.PostId = b.PostId group by b.TagName ) as recentCol INNER JOIN ( select d.TagName as Tag, count(c.PostId) as TotalCnt from (select PostId from Tags, PostTags where Tags.Id = TagId and TagName = 'javascript' ) as c INNER JOIN (select PostId, Tags.TagName from Tags, PostTags, Posts where Tags.Id = TagId and PostId = Posts.Id and TagName not like '%javascript%' and CreationDate > DATEADD(year , -4, GETDATE()) and CreationDate < DATEADD(year , -1, GETDATE()) ) as d on c.PostId = d.PostId group by d.TagName having count(c.PostId) > 3000 ) as TotalCol on RecentCol.Tag = TotalCol.Tag order by recentCol.recentCnt desc;