How many edits have I made to titles vs. posts?

2

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

-- How many edits have I made and how much more until I earn the badges?
-- 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.

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 @PostEdits int
DECLARE @TitleEdits int

SELECT @PostEdits = COUNT(DISTINCT h.RevisionGUID)
    FROM PostHistory h
    WHERE h.UserId = @UserId
      AND h.PostHistoryTypeId IN (@EditBody)
      AND h.PostId NOT IN (SELECT p.Id
                               FROM Posts p
                               WHERE p.OwnerUserId = h.UserId)

SELECT @TitleEdits = COUNT(DISTINCT h.RevisionGUID)
    FROM PostHistory h
    WHERE h.UserId = @UserId
      AND h.PostHistoryTypeId IN (@EditTitle)
      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

SELECT @TitleEdits as TitleEdits,
       @PostEdits as PostEdits

Enter Parameters

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