Number of questions for given tags in the last 32 weeks pivoted

0

Please login or register to vote for this query.

(click on this box to dismiss)

To get an idea of the activity of given tags this query filters all question tagged with given tags.

Cognitive Sciences Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

CREATE TABLE #TagFilter (t int              
              , FilterTagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO #TagFilter (t, FilterTagName) VALUES 
        (1, 'angular'),
        (2, 'reactjs');
        

DECLARE @PostCreatedAfter smalldatetime
SET @PostCreatedAfter = DATEADD(YEAR, -3, GETDATE()) 

SELECT * FROM 
(
   SELECT 
      Tags.TagName               
     -- ,(DATEPART(WEEK, Posts.CreationDate)*1) as Week
     ,(DATEDIFF(YEAR,Posts.CreationDate, @PostCreatedAfter)*1) as YearsAgo
     -- ,DATEPART(year, Posts.CreationDate)*100 + DATEPART(month, Posts.CreationDate) as YearMonth     
     ,Count(Posts.Id) as NrOfPosts     
    FROM Tags
       INNER JOIN #TagFilter ON Tags.TagName = #TagFilter.FilterTagName
       INNER JOIN PostTags ON Tags.Id = PostTags.TagId
       INNER JOIN Posts ON Posts.Id = PostTags.PostId                    
    WHERE Posts.CreationDate >=  @PostCreatedAfter
    GROUP BY Tags.TagName     
     -- ,DATEPART(YEAR, Posts.CreationDate)
     ,DATEDIFF(YEAR,Posts.CreationDate, @PostCreatedAfter)
     -- Order By Tags.TagName, Week
    --Having Count(Posts.Id) > 1
) src
PIVOT (
   sum(NrOfPosts)
   for YearsAgo in ([-3],[-2],[-1],[0])
) piv

Enter Parameters

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