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
  , q.[Month]
  , q.CountQuestions
  , a.CountAnswers
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(*) CountQuestions
    FROM Posts p
      INNER JOIN posttags pt on p.id=pt.postid
    where 
      p.posttypeid = 1
    group by pt.tagid, CAST(
            CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) + '-01'
        AS DATETIME)
    )  q on t.id = q.tagid
    left outer join ( */
        select pt.tagid
        , CAST(
            CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) + '-01' AS DATETIME) [Month]
      , count(*) CountAnswers
    FROM Posts p
      INNER JOIN posttags pt on p.id=pt.postid
    WHERE 
      p.posttypeid = 1
      and pt.TagId IN 
  (
    SELECT TOP 5 Id FROM Tags 
    ORDER BY Count DESC
  )
      and CAST(
            CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) + '-01' AS DATETIME) >= CAST('2012-04-01' as datetime)
    GROUP BY pt.tagid, CAST(
            CAST(DATEPART(YYYY, p.CreationDate) AS varchar) + '-' 
            + CAST(DATEPART(MM, p.CreationDate) AS varchar) + '-01' AS DATETIME)

    /*) a on q.tagid = a.tagid and q.[Month] = a.[Month]

where t.id IN (
    SELECT TOP 5 Id FROM Tags 
    ORDER BY Count DESC
  ) 
  and q.[Month] >= CAST('2012-04-01' as datetime)
  AND q.[Month] < CAST('2012-07-01' as datetime)
order by q.[Month], TagName 
*/
/*
*, COUNT(*) AS Count FROM
(
  SELECT CAST(
      CAST(DATEPART(YYYY, CreationDate) AS varchar) + '-' + CAST(DATEPART(MM, CreationDate) AS varchar) + '-01'
    AS DATETIME) [Month], 
    t.TagName
  FROM Posts p 
  JOIN PostTags pt ON pt.PostId = p.Id
  JOIN Tags t ON t.Id = pt.TagId
  WHERE TagId IN 
  (
    SELECT TOP 5 Id FROM Tags 
    ORDER BY Count DESC
  )
) AS X
WHERE [Month] >= CAST('2012-04-01' as datetime)
AND [Month] < CAST('2012-07-01' as datetime)
GROUP BY TagName, [Month]
ORDER BY [Month] ASC, TagName ASC
*/

Enter Parameters

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