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

0

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. Logic based on the older way of counting the edits. Final version: http://data.stackexchange.com/stackoverflow/s/1024

Anime and Manga

Q&A for anime and manga fans

-- How many edits have I made and how much more until I earn the badges? (v1)
-- 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 the older way of counting the edits.
-- 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 @EditCount int

SELECT @EditCount = COUNT(*)
    FROM PostHistory h
    WHERE h.UserId = @UserId
      AND h.PostId NOT IN (SELECT p.Id
                               FROM Posts p
                               WHERE p.Id = h.PostId
                                 AND p.OwnerUserId = h.UserId)

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"

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results