37

Please login or register to vote for this query.

(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.

Q&A for users of Linux, FreeBSD and other Un*x-like operating systems

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"

fork query

created feb 25 11 Jeff Mercado