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.
Q&A about the site for people interested in the history and origins of science and mathematics
DECLARE @UserId int = ##UserId## -- User id to search for DECLARE @Badge1 int = 1 -- Editor edit count DECLARE @Badge2 int = 80 -- Strunk & White edit count DECLARE @Badge3 int = 500 -- Copy Editor edit count DECLARE @EditTitle int = 4 DECLARE @EditBody int = 5 DECLARE @PostDelete int = 12 DECLARE @PostUndelete int = 13 DECLARE @EditCount int SELECT @EditCount = COUNT(DISTINCT h.RevisionGUID) FROM PostHistory h WHERE h.UserId = @UserId AND h.PostHistoryTypeId IN (@EditTitle, @EditBody) AND h.PostId NOT IN (SELECT p.Id FROM Posts p WHERE p.OwnerUserId = h.UserId) -- deleted posts do not appear in the results so not necessary -- AND (SELECT SUM(CASE h2.PostHistoryTypeId -- WHEN @PostUndelete THEN 1 -- WHEN @PostDelete THEN -1 -- ELSE 0 END) -- FROM PostHistory h2 -- WHERE h.Id = h2.Id) >= 0 DECLARE @UntilBadge1 int = CASE WHEN (@Badge1 - @EditCount) < 0 THEN 0 ELSE @Badge1 - @EditCount END DECLARE @UntilBadge2 int = CASE WHEN (@Badge2 - @EditCount) < 0 THEN 0 ELSE @Badge2 - @EditCount END DECLARE @UntilBadge3 int = CASE WHEN (@Badge3 - @EditCount) < 0 THEN 0 ELSE @Badge3 - @EditCount END SELECT @UserId AS [User Link], @EditCount AS "Edit count", @UntilBadge1 AS "Edits needed to earn 'Editor' badge", @UntilBadge2 AS "Edits needed to earn 'Strunk & White' badge", @UntilBadge3 AS "Edits needed to earn 'Copy Editor' badge"