Generic Query for Trending Multiple Tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query is somewhat generic so that I can see trending for multiple tags. In this example I'm comparing different JavaScript Frameworks/Libraries, but these Tag names can be easily changed to any tag names you want. For a visual comparison, in the result click "Graph" tab. Very cool feature. I tried to make this a fast a possible by reviewing the execution plan. This probably can be made even faster, but happy with it now.

Music Fans Meta

Q&A about the site for music historians, critics, and fans

--Get the Tag IDs that you are interested in for the Tags table
SELECT Id, TagName 
INTO #MyTags
FROM Tags
WHERE 
  TagName IN ('angularjs', 'backbone.js', 'knockout.js', 'ember.js') 


--Easily change date parameters
DECLARE @StartDate DATETIME = '2012-1-1'
        ,@EndDate DATETIME = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- First Day Of current Month

--
SELECT RoundToMonth, TagName, TagCount
FROM 
(
  SELECT  
    DATEADD(Month, DATEDIFF(MONTH, 0, CreationDate), 0) RoundToMonth
    , COUNT(PostTags.TagID) TagCount
    , TagID
  FROM PostTags
  LEFT JOIN Posts ON
    PostTags.PostId = Posts.Id
  WHERE 
    PostTags.TagID IN (SELECT Id FROM #MyTags)
    AND CreationDate > @StartDate
    AND CreationDate < @EndDate
  GROUP BY
    DATEADD(Month, DATEDIFF(MONTH, 0, CreationDate), 0)
    , PostTags.TagID
)TagCountPerMonth
LEFT JOIN Tags ON
  TagCountPerMonth.TagId = Tags.Id
ORDER BY RoundToMonth DESC, TagName ASC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results