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 TopTags AS
(SELECT PostTags.TagId as TagId, Count(PostTags.PostId) AS [Number of Posts]
FROM PostTags INNER JOIN (SELECT * FROM Tags WHERE TagName = ##TagName:string##) as A on PostTags.TagId = A.Id
     GROUP BY TagId),
     
     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) 

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

view current version

edited jan 26

created jan 26 jon.doe41074