Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation
WITH Raw AS ( SELECT Tags.Id AS TagId, W.OwnerUserId AS UserId FROM Tags, Posts W, Posts E WHERE Tags.ExcerptPostId = E.Id AND Tags.WikiPostId = W.Id AND E.OwnerUserId = W.OwnerUserId AND E.OwnerUserId > 0 ), Raw2 AS ( SELECT Posts.OwnerUserId AS UserId, PostTags.TagId, SUM(Posts.Score) AS TagScore FROM Posts, PostTags WHERE PostTags.TagId IN (SELECT TagId FROM Raw) AND Posts.ParentId = PostTags.PostId AND Posts.OwnerUserId > 0 GROUP BY Posts.OwnerUserId, PostTags.TagId ), Raw3 AS ( SELECT TagId, MAX(TagScore) AS MaxTagScore FROM Raw2 GROUP BY TagId ), Raw4 AS ( SELECT Raw2.TagId, Raw2.UserId FROM Raw2, Raw3 WHERE Raw2.TagId = Raw3.TagId AND Raw3.MaxTagScore = Raw2.TagScore ), Raw5 AS ( SELECT * FROM Raw INTERSECT SELECT * FROM Raw4 ) SELECT Raw5.UserId AS [User Link], Tags.TagName, Tags.Count, Raw3.MaxTagScore FROM Raw5, Raw3, Tags WHERE Raw5.TagId = Raw3.TagId AND Raw5.TagId = Tags.Id ORDER BY Tags.Count DESC, Raw5.TagId