Top 5 monthly tags over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Russian Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

SELECT *

FROM (

  SELECT
  Question_Year,
  Question_Month,
  Tag,
  Question_Count,
  ROW_NUMBER() OVER (PARTITION BY Question_Year,Question_Month
                     ORDER BY Question_Count DESC) AS Question_Rank
  FROM (

    SELECT
    DATEPART(YEAR, p.CreationDate) as Question_Year,
    DATEPART(MONTH, p.CreationDate) as Question_Month,
    t.TagName AS Tag,
    COUNT(DISTINCT p.Id) AS Question_Count

    FROM Posts AS p

    INNER JOIN PostTypes as ptypes
      ON p.PostTypeId = ptypes.Id
      AND ptypes.Name = 'Question'

    INNER JOIN PostTags AS ptags
      ON p.Id = ptags.PostId

    INNER JOIN Tags AS t
      ON t.Id = ptags.TagId

    GROUP BY 
    DATEPART(YEAR, p.CreationDate),
    DATEPART(MONTH, p.CreationDate),
    t.TagName

    ) AS SUB_1

) AS SUB_2

WHERE Question_Rank <= 5

ORDER BY 
Question_Year,
Question_Month,
Question_Rank DESC

Enter Parameters

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