0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

With Answers AS 
(SELECT * FROM PostTags INNER JOIN 
(SELECT Posts.Id, PostTypeId, ParentId, YEAR(Posts.CreationDate) as CreationYear, OwnerUserId, Tags, Reputation
FROM Posts INNER JOIN Users on Posts.OwnerUserId = Users.Id WHERE PostTypeId = 2) as A on PostTags.PostId = A.ParentId),

SelectTag AS
     (SELECT PostTags.TagId as TagId, TagName, Count(PostTags.PostId) AS [Number of Posts]
     FROM PostTags INNER JOIN (SELECT * FROM Tags WHERE 
     TagName = 'docker' or 
     TagName = 'terraform' or 
     TagName = 'elasticsearch' or 
     TagName = 'travis' or 
     TagName = 'kubernetes'
     ) as A on PostTags.TagId = A.Id
     GROUP BY TagId, TagName)


SELECT CreationYear, [Post Count], TagName, COUNT(OwnerUserId) AS [Number of Users] 
FROM

  (SELECT CreationYear, OwnerUserId, TagName, COUNT(DISTINCT PostId) as [Post Count]
   FROM 
   SelectTag
   INNER JOIN
   Answers
   on Answers.TagId = SelectTag.TagId
   GROUP BY CreationYear, OwnerUserId, TagName) as Y
   
GROUP BY CreationYear, [Post Count], TagName;

view current version

edited jan 26

created jan 26 jon.doe41074