Number of edits for badge wins

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

SELECT b.UserId, b.Name, b.Date
INTO #UsersWhoGotBadge
FROM Badges b
WHERE 
    b.Name = 'Strunk & White' AND
    b.Date > '2011-02-23'; -- it is important to run this query 
                           -- after 2011-02-22 as that is when the 
                           -- requirements for this badge changed
                           -- but this 2016 date is just to sub-sample 
                           -- some of the results.


-- Find the edits that a user made on his/her
-- own posts to avoid counting these.
SELECT 
   ph.Id, ph.UserId
INTO 
   #EditsToMyPosts
FROM 
   PostHistory ph
INNER JOIN #UsersWhoGotBadge b
    ON (b.UserId = ph.UserId)
INNER JOIN Posts p
    ON (p.Id = ph.PostId AND p.OwnerUserId = ph.UserId)
WHERE ( p.PostTypeId in (1,2) );

-- Count the number of edits that a user makes until (inclusive of)
-- the day when he/she obtained the Strunk & White badge
SELECT 
    t.UserId,
    COUNT(DISTINCT(t.RevisionGUID)) edit_count
FROM (
  SELECT ph.UserId,
         ph.RevisionGUID 
  FROM PostHistory ph
  INNER JOIN #UsersWhoGotBadge b
    ON ( 
       b.UserId = ph.UserId AND
       ph.PostHistoryTypeId  in (4,5) AND     -- include edits to 
                                              -- title and body
       ph.Id NOT IN (SELECT m.Id 
                     FROM #EditsToMyPosts m 
                     WHERE m.UserId = ph.UserId) -- don't include edits to 
                                                 -- my own posts
    )
   GROUP BY ph.UserId, ph.RevisionGUID  -- don't count when I 
                                        -- edit the same post
                                        -- more than once
) t
GROUP BY UserId
ORDER BY edit_count DESC;

Enter Parameters

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