How many edits have I made and how much more until I earn the badges? (v2)

4

(click on this box to dismiss)

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 serious players and enthusiasts of chess

``````-- 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",