Top 1k Tags: Users that ask and answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH TopTags as
(SELECT TOP 1500 Id, TagName, Tags.Count FROM Tags ORDER BY Count desc),

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

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

PostsDate AS
(SELECT * FROM PostsUpdated WHERE Year(CreationDate) = '##Year##'),

UsersOnTags AS
(SELECT DISTINCT TagName, Users.Id, COUNT(PostTypeId) as CountIds FROM PostsDate Inner Join Users on PostsDate.OwnerUserId = Users.Id WHERE PostTypeId=1 or PostTypeId=2 GROUP BY TagName, Users.Id, PostTypeId )

SELECT TagName, COUNT(Case when CountIds > 2 then Id end) as NumBoth, Count(Id) as NumUsers FROM UsersOnTags GROUP BY TagName;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results