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

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- 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 @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 = 5
      AND h.PostId NOT IN (SELECT p.Id
                               FROM Posts p
                               WHERE p.OwnerUserId = h.UserId)

SELECT  u.DisplayName as [User], max(u.Reputation) as [Rep], COUNT(DISTINCT ht.RevisionGUID)  as [Title edits],
COUNT(DISTINCT hp.RevisionGUID) as [Post edits], 
COUNT(DISTINCT ht.RevisionGUID) + 
COUNT(DISTINCT hp.RevisionGUID) as total
FROM USERS u 
LEFT OUTER JOIN PostHistory hp
ON hp.UserId = u.Id AND hp.PostHistoryTypeId = 5
JOIN Posts pp 
ON pp.id = hp.PostId AND pp.OwnerUserId <> u.Id
LEFT OUTER JOIN PostHistory ht
ON ht.UserId = u.Id AND ht.PostHistoryTypeId = 4
JOIN Posts pt 
ON pt.id = ht.PostId AND pt.OwnerUserId <> u.Id
GROUP by u.displayname
HAVING COUNT(DISTINCT ht.RevisionGUID) + 
COUNT(DISTINCT hp.RevisionGUID) >200
ORDER by total desc

Enter Parameters

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