Top 5 tags/technologies by year since 2012 [top, start year modifiable]

0

Please login or register to vote for this query.

(click on this box to dismiss)

Reference - http://data.stackexchange.com/stackoverflow/query/162567/technology-trends-questions-per-tag-per-month#messages

Computational Science

Q&A for scientists using computers to solve scientific problems

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  [Year],
  [TagName],
  [Questions] 
  
FROM (
    SELECT
      YEAR(Posts.CreationDate) AS [Year],
      Tags.TagName,
      COUNT(*) AS Questions,
      ROW_NUMBER() OVER(PARTITION BY YEAR(Posts.CreationDate) ORDER BY COUNT(*) DESC) as rownum 

    FROM Tags
      LEFT JOIN PostTags ON PostTags.TagId = Tags.Id
      LEFT JOIN Posts ON Posts.Id = PostTags.PostId
      LEFT JOIN PostTypes ON PostTypes.Id = Posts.PostTypeId

    WHERE
      
      PostTypes.Name = 'Question' AND
      --Tags.TagName like '%sql%' AND -- Custom Tags
      Posts.CreationDate>'20200201' -- From the year 2012

    GROUP BY
    Tags.TagName, YEAR(Posts.CreationDate)
    HAVING Count(*)>1000
) AS A

WHERE A.rownum<=1000

ORDER BY
 [Year] ASC, rownum ASC

Enter Parameters

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