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.

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

--Get the Tag IDs that you are interested in for the Tags table
SELECT Id, TagName 
INTO #MyTags
FROM Tags
WHERE 
  TagName IN ('backbone.js', 'knockout.js', 'ember.js') 
  --TagName IN ('android', 'ipad', 'iphone', 'blackberry', 'windows-mobile') 
  --TagName IN ('sql-server', 'oracle', 'mysql', 'mongodb', 'postgresql') 
  --TagName IN ('javascript', 'java', 'c#', 'vb.net', 'ruby-on-rails', 'ruby', 'php') 
  --TagName IN ('bing-maps', 'google-maps') 
  --TagName IN ('opengl', 'cuda')
  --TagName IN ('fody', 'postsharp')


--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 sites:
loading Hold tight while we fetch your results