0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

WITH TagNames AS
(SELECT * FROM Tags WHERE TagName='node.js'),

UpdatedPostTags AS
(SELECT PostId, TagId, TagName FROM PostTags INNER JOIN TagNames on TagNames.Id = TagId),

Posts2 AS 
(SELECT * FROM Posts INNER JOIN UpdatedPostTags on UpdatedPostTags.PostId = (case when (ParentId is null) then Posts.Id else ParentId END)),

PostsByUser AS

  (SELECT YEAR(CreationDate) as CreationYear, TagName, OwnerUserId, COUNT(Id) as NumberPosts
  FROM Posts2
  GROUP BY YEAR(CreationDate), OwnerUserId, TagName),

TagsPerUser AS

(SELECT TagName, OwnerUserId, Year(CreationDate) as CreationYear, SUM(NumberTags) as NumberTags 
FROM Posts2 INNER JOIN (SELECT PostId, COUNT(TagId) as NumberTags FROM PostTags GROUP BY PostId) as Y on Y.PostId = Id
GROUP BY OwnerUserId, YEAR(CreationDate), Posts2.TagName),

TopUsers AS
(SELECT * FROM (SELECT OwnerUserId, Count(Id) as NumberPosts, TagName, ROW_NUMBER() OVER(PARTITION BY TagName ORDER BY Count(ID) DESC) AS RN FROM Posts2 GROUP BY OwnerUserId, TagName) as X WHERE RN <= 8000)

/*TopUsers AS

  (SELECT TOP 10000 OwnerUserId, COUNT(Id) as NumberPosts
  FROM Posts2
  GROUP BY OwnerUserId
  ORDER BY COUNT(ID) DESC)*/
  

SELECT TagsPerUser.TagName, TagsPerUser.OwnerUserId, 
max(CASE WHEN CreationYear = '2008' THEN TagsPerUser.NumberTags END) as Activity_2008,
max(CASE WHEN CreationYear = '2009' THEN TagsPerUser.NumberTags END) as Activity_2009,
max(CASE WHEN CreationYear = '2010' THEN TagsPerUser.NumberTags END) as Activity_2010,
max(CASE WHEN CreationYear = '2011' THEN TagsPerUser.NumberTags END) as Activity_2011,
max(CASE WHEN CreationYear = '2012' THEN TagsPerUser.NumberTags END) as Activity_2012,
max(CASE WHEN CreationYear = '2013' THEN TagsPerUser.NumberTags END) as Activity_2013,
max(CASE WHEN CreationYear = '2014' THEN TagsPerUser.NumberTags END) as Activity_2014,
max(CASE WHEN CreationYear = '2015' THEN TagsPerUser.NumberTags END) as Activity_2015,
max(CASE WHEN CreationYear = '2016' THEN TagsPerUser.NumberTags END) as Activity_2016,
max(CASE WHEN CreationYear = '2017' THEN TagsPerUser.NumberTags END) as Activity_2017,
max(CASE WHEN CreationYear = '2018' THEN TagsPerUser.NumberTags END) as Activity_2018,
max(CASE WHEN CreationYear = '2019' THEN TagsPerUser.NumberTags END) as Activity_2019,
max(CASE WHEN CreationYear = '2020' THEN TagsPerUser.NumberTags END) as Activity_2020,
max(CASE WHEN CreationYear = '2021' THEN TagsPerUser.NumberTags END) as Activity_2021,
max(CASE WHEN CreationYear = '2022' THEN TagsPerUser.NumberTags END) as Activity_2022
FROM TagsPerUser JOIN TopUsers on TagsPerUser.OwnerUserId = TopUsers.OwnerUserId and TagsPerUser.TagName = TopUsers.TagName
GROUP BY TagsPerUser.OwnerUserId, TagsPerUser.TagName;

fork query

edited mar 31

created mar 2 jon.doe41074