Q&A about the site for expert genealogists and people interested in genealogy or family history
-- UserId: User ID "Enter a user's ID." WITH eligableTags AS ( -- There is some cutoff; this is best SWAG at moment SELECT t.Id FROM Tags t INNER JOIN PostTags pt ON t.Id = pt.TagId INNER JOIN Posts q ON q.Id = pt.PostId GROUP BY t.Id HAVING Count (q.ID) >= 200 ), answerScoreByUserAndTag AS ( SELECT et.Id AS TagId , a.OwnerUserId , Sum ( CASE WHEN a.CommunityOwnedDate IS NULL THEN a.Score ELSE 0 END ) AS [Score] , Count (a.Id) AS [Elgbl Ans] , Sum ( CASE WHEN a.CommunityOwnedDate IS NULL THEN 0 ELSE 1 END ) AS [CW ans] FROM eligableTags et INNER JOIN PostTags pt ON et.Id = pt.TagId INNER JOIN Posts q ON q.Id = pt.PostId INNER JOIN Posts a ON a.ParentId = q.Id WHERE a.OwnerUserId IS NOT NULL AND a.Score > 0 GROUP BY et.Id, a.OwnerUserId -- There might also be user rep cutoffs? ), targUsersEligableTags AS ( SELECT asbut.TagId , (SELECT t.TagName FROM Tags t WHERE t.Id = asbut.TagId) AS TagName , asbut.OwnerUserId AS TargUser , asbut.Score AS tuScore , asbut.[CW ans] , asbut.[Elgbl Ans] FROM answerScoreByUserAndTag asbut WHERE asbut.OwnerUserId = ##UserId:int## AND asbut.Score > 0 ), tagStats AS ( SELECT tut.TagId , Count (asbut.OwnerUserId) AS ttlUsers , Sum ( CASE WHEN asbut.Score > tut.tuScore THEN 1 ELSE 0 END ) AS bttrUsers FROM targUsersEligableTags tut INNER JOIN answerScoreByUserAndTag asbut ON ( asbut.TagId = tut.TagId AND asbut.OwnerUserId <> tut.TargUser ) GROUP BY tut.TagId ) SELECT -- TOP 50 -- Top limit here really doesn't save anything tut.TagName -- Do not rename. `TagName` is magic in SEDE , ( CASE WHEN ts.bttrUsers = 0 THEN 'Top User!' ELSE Format (100.0 * ts.bttrUsers / ts.ttlUsers, 'N1') END ) AS [Percentile] , tut.tuScore AS [Ans Score] , tut.[Elgbl Ans] , tut.[CW Ans] FROM targUsersEligableTags tut INNER JOIN tagStats ts ON ts.TagId = tut.TagId ORDER BY (100.0 * ts.bttrUsers / ts.ttlUsers) ASC -- Subdecimal sorting borked, hence the * 100 , tut.TagName