Top 1k Tags: Existing Users

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, Users.CreationDate, Users.LastAccessDate FROM PostsDate Inner Join Users on PostsDate.OwnerUserId = Users.Id)

SELECT TagName, AVG(CASE WHEN Year(CreationDate) < '##Year##' THEN CAST(('##Year##' - YEAR(CreationDate)) as float) Else 0 END) as AvgTime FROM UsersOnTags GROUP By TagName;

Enter Parameters

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