Q&A for professional and enthusiast programmers
-- How many edits have I made and how much more until I earn the badges? -- Determines how many edits a user has made as well as how many more edits until the `Editor`, `Strunk & White` and `Copy Editor` badges are earned. DECLARE @UserId int = ##UserId## -- User id to search for DECLARE @EditTitle int = 4 DECLARE @EditBody int = 5 DECLARE @PostDelete int = 12 DECLARE @PostUndelete int = 13 DECLARE @PostEdits int DECLARE @TitleEdits int SELECT @PostEdits = COUNT(DISTINCT h.RevisionGUID) FROM PostHistory h WHERE h.UserId = @UserId AND h.PostHistoryTypeId = 5 AND h.PostId NOT IN (SELECT p.Id FROM Posts p WHERE p.OwnerUserId = h.UserId) SELECT u.DisplayName as [User], max(u.Reputation) as [Rep], COUNT(DISTINCT ht.RevisionGUID) as [Title edits], COUNT(DISTINCT hp.RevisionGUID) as [Post edits], COUNT(DISTINCT ht.RevisionGUID) + COUNT(DISTINCT hp.RevisionGUID) as total FROM USERS u LEFT OUTER JOIN PostHistory hp ON hp.UserId = u.Id AND hp.PostHistoryTypeId = 5 JOIN Posts pp ON pp.id = hp.PostId AND pp.OwnerUserId <> u.Id LEFT OUTER JOIN PostHistory ht ON ht.UserId = u.Id AND ht.PostHistoryTypeId = 4 JOIN Posts pt ON pt.id = ht.PostId AND pt.OwnerUserId <> u.Id GROUP by u.displayname HAVING COUNT(DISTINCT ht.RevisionGUID) + COUNT(DISTINCT hp.RevisionGUID) >200 ORDER by total desc