Questions Per Tag (Top Five) (By Month)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

SELECT 
  t.tagName
  , p.[Month]
  , p.PostCount
From 
  Tags t
  INNER JOIN (
      SELECT 
        pt.TagId
        , CAST(CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) 
            + '-01' AS DATETIME) [Month]
        , count(*) PostCount
      FROM
        Posts p
        INNER JOIN PostTags pt on p.Id=pt.PostId 
      where p.PostTypeId = 1
      group by 
        TagId
        , CAST(CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) 
            + '-01' AS DATETIME)
      ) p on t.id = p.TagId
  INNER JOIN (
    -- Answers don't have tags, need to refer to the parent question
    SELECT 
      pt.TagId
      , CAST( CAST(DATEPART(YYYY, pans.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, pans.CreationDate) AS varchar) 
            + '-01' AS DATETIME) [Month]
      , count(*) AnswerCount
    FROM
      Posts pans
      INNER JOIN Posts pq on pans.parentId = pq.Id
      INNER JOIN PostTags pt on pq.Id = pt.PostId
    WHERE 
      pans.PostTypeId = 2
      and pq.PostTypeId = 1
    GROUP BY
      pt.TagId
      , CAST( CAST(DATEPART(YYYY, pans.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, pans.CreationDate) AS varchar) 
            + '-01' AS DATETIME)
    
  ) pa on t.id = pa.TagId
    and p.[Month] = pa.[Month]
  

where p.TagId in
  (
    SELECT TOP 5 Id FROM Tags 
    ORDER BY Count DESC
  )
  AND p.[Month] >= CAST('2012-04-01' as datetime)
  AND p.[Month] < CAST('2012-07-01' as datetime)
  
ORDER BY tagName, [Month]

Enter Parameters

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