Shows the top 500 post editors
Вопрос и ответы о сайте для лингвистов и русских энтузиастов
-- Top 500 Most Prolific Editors -- Shows the top 500 post editors SELECT TOP 500 COUNT(DISTINCT ph.PostId) AS 'Posts edits', -- every post counts once only COUNT(DISTINCT CASE WHEN p.PostTypeId = 1 THEN ph.PostId ELSE NULL END) AS 'Question edits', COUNT(DISTINCT CASE WHEN p.PostTypeId = 2 THEN ph.PostId ELSE NULL END) AS 'Answer edits', COUNT(DISTINCT CASE WHEN p.PostTypeId >= 3 THEN ph.PostId ELSE NULL END) AS 'Wiki edits', COUNT(DISTINCT ph.RevisionGUID) AS 'Revisions edits', -- every revision counts once only SUM(CASE WHEN ph.PostHistoryTypeId = 4 THEN 1 ELSE 0 END) AS 'Titles edits', SUM(CASE WHEN ph.PostHistoryTypeId = 5 THEN 1 ELSE 0 END) AS 'Bodies edits', SUM(CASE WHEN ph.PostHistoryTypeId = 6 THEN 1 ELSE 0 END) AS 'Tags edits', ph.UserId AS [User Link] FROM PostHistory ph JOIN Posts p ON p.Id = ph.PostId AND p.OwnerUserId <> ph.UserId AND ph.Comment LIKE '%dogs%' GROUP BY ph.UserId ORDER BY 'Posts edits' DESC