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 
     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),
     
     X AS
     (SELECT PostTags.TagId as Id_Tag, PostTags.PostId as Id_Post, OwnerUserId, YEAR(UserPosts.CreationDate) as CreationYear, ViewCount, AnswerCount
     FROM 
     PostTags INNER JOIN
     (SELECT Posts.Id, PostTypeId, Posts.CreationDate, OwnerUserId, Tags, ViewCount, AnswerCount
     FROM Posts INNER JOIN Users on Posts.OwnerUserId = Users.Id) as UserPosts 
     on PostTags.PostId = UserPosts.Id WHERE PostTypeId = 1)

SELECT CreationYear, [Post Count], TagName, COUNT(OwnerUserId) AS [Number of Users], SUM(NumberViews) as [Number of Views], CAST(SUM([Answer Count]) as float) / CAST((COUNT(OwnerUserId) * [Post Count]) as float) as [Avg Answer Count] 
FROM

  (SELECT CreationYear, OwnerUserId, TagName, SUM(ViewCount) as [NumberViews], COUNT(DISTINCT Id_Post) as [Post Count], SUM(AnswerCount) as [Answer Count]
   FROM 
   SelectTag
   INNER JOIN
   X
   on X.Id_tag = 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