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.

Stack Overflow em Português

Perguntas e respostas para programadores profissionais e entusiastas

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