Most edited Posts, among posts with all 7 major edit types

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

SELECT TOP 200
            ph.PostId AS [Post Link]
            , 'site://posts/' + CAST (ph.PostId AS NVARCHAR) + '/revisions|' + 'Revision History' AS [Revisions List]
            , COUNT (DISTINCT ph.RevisionGUID) AS [Numb Edits]
            , SUM (CASE WHEN ph.PostHistoryTypeId = 24  THEN 1  ELSE 0  END) AS [Suggested Edits]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  4  THEN 1  ELSE 0  END) AS [Title Edts]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  5  THEN 1  ELSE 0  END) AS [Body Edts]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  6  THEN 1  ELSE 0  END) AS [Tag Edts]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  7  THEN 1  ELSE 0  END) AS [Title RollBacks]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  8  THEN 1  ELSE 0  END) AS [Body RollBacks]
            , SUM (CASE WHEN ph.PostHistoryTypeId =  9  THEN 1  ELSE 0  END) AS [Tag RollBacks]
FROM        PostHistory ph
WHERE       ph.PostHistoryTypeId IN (4, 5, 6, 7, 8, 9, 24)  -- Edit: Title, Body, Tags; Rollback: Title, Body, Tags; Suggested Edit
GROUP  BY   ph.PostId
HAVING      SUM (CASE WHEN ph.PostHistoryTypeId = 24  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  4  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  5  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  6  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  7  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  8  THEN 1  ELSE 0  END)  > 0
AND         SUM (CASE WHEN ph.PostHistoryTypeId =  9  THEN 1  ELSE 0  END)  > 0
ORDER BY    [Numb Edits] DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results