Posts, Answers, Comments, Favourits, and Views By Month Year, and Tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

SELECT
  
  [PostsRanked].*

FROM

  (

     SELECT

      [PostsAgg].*,
      ROW_NUMBER() OVER(
        PARTITION BY 
          [PostsAgg].[Tags]
        ORDER BY 
          [PostsAgg].[PostYear] DESC,
          [PostsAgg].[PostMon] DESC,
          [PostsAgg].[NumberOfTags] DESC
       ) AS TagMonRank

    FROM 

      (

        SELECT 

          MONTH([Posts].[CreationDate]) AS PostMon,
          YEAR([Posts].[CreationDate]) AS PostYear,
          [Posts].[Tags],
          COUNT(*) as NumberOfTags, 
          SUM([Posts].[AnswerCount]) AS NumberOfAnswers,
          SUM([Posts].[CommentCount]) AS NumberOfComments,
          SUM([Posts].[FavoriteCount]) AS NumberOfFavourites,
          SUM([Posts].[ViewCount]) AS NumberOfViews

        FROM

          [Posts]

        WHERE

          [Posts].[Tags] IS NOT NULL 

        GROUP BY 

          MONTH([Posts].[CreationDate]),
          YEAR([Posts].[CreationDate]),
          [Posts].[Tags]

        ) PostsAgg

   ) PostsRanked

WHERE

  [PostsRanked].[TagMonRank] <= 10

ORDER BY 

  [PostsRanked].[PostMon] DESC,
  [PostsRanked].[PostYear] DESC,
  [PostsRanked].[NumberOfTags] DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results