3

Please login or register to vote for this query.

(click on this box to dismiss)

Previous queries haven't taken into consideration all the requirements laid out in http://meta.stackexchange.com/questions/67397/list-of-all-badges-with-full-descriptions#188732.

Q&A for information security professionals

DECLARE @user INT = ##UserId:int##
DECLARE @edits INT

SELECT @edits = COUNT(*)
FROM (
  SELECT 
  PH.PostId, P.*
  , ROW_NUMBER() OVER (PARTITION BY PH.PostId ORDER BY PH.CreationDate ASC) [row]
  FROM PostHistory PH
  INNER JOIN Posts as P on PH.PostId=P.Id
  -- Not editing own posts
  WHERE PH.UserId=@user and P.OwnerUserId <> @user
  -- Only question, answers, community wikis and tag wikis
  AND P.PostTypeId IN (1, 2, 3, 4, 5)
  -- Only title and body edits qualify
  AND PH.PostHistoryTypeId in (4, 5)
  -- Edits on deleted posts don't count
  AND P.ClosedDate IS NULL
) P
-- Multiple edits to same post don't count
WHERE P.[row] = 1

SELECT
@edits [Total Valid Edits],
CAST(ROUND(@edits * 1.25, 2) as VARCHAR(10)) + '%' as [Strunk And White],
CAST(ROUND(@edits / 5.0, 2) as VARCHAR(10)) + '%' as [Copy Editor]

fork query

edited feb 26 15

created feb 26 15 Lankymart