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. Logic based on Waffles' explanation: http://meta.stackoverflow.com/questions/79054/copy-editor-with-too-few-revisions/80260#80260 Counts posts that have been edited. Final version: http://data.stackexchange.com/stackoverflow/s/1024
Q&A for anime and manga fans
-- How many edits have I made and how much more until I earn the badges? (v2) -- 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. -- Logic based on Waffles' explanation: http://meta.stackoverflow.com/questions/79054/copy-editor-with-too-few-revisions/80260#80260 -- Counts posts that have been edited. -- Final version: http://data.stackexchange.com/stackoverflow/s/1024 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(*) FROM Posts p WHERE p.OwnerUserId != @UserId AND EXISTS (SELECT * FROM PostHistory h WHERE p.Id = h.PostId AND h.UserId = @UserId AND h.PostHistoryTypeId IN (@EditTitle, @EditBody)) -- deleted posts do not appear in the results so not necessary -- AND (SELECT SUM(CASE h.PostHistoryTypeId -- WHEN @PostUndelete THEN 1 -- WHEN @PostDelete THEN -1 -- ELSE 0 END) -- FROM PostHistory h -- WHERE p.Id = h.PostId) >= 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 @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"