Number of edits before and after CW


Please login or register to vote for this query.

(click on this box to dismiss)

Literature Meta

Q&A about the site for scholars and enthusiasts of literature

;with cw_cte as
  ph.Id AS cwid,
  ph.PostId AS pid
FROM PostHistory ph INNER JOIN Posts p ON ph.PostId=p.Id
WHERE (ph.PostHistoryTypeId=16)
---16 = Community Owned - post now community owned

SELECT TOP(##num?100##)
---  COUNT (DISTINCT RevisionGUID) AS [All edits],
  COUNT (DISTINCT ph.Id) AS [All edits],
---notice that title/body/tags edits are counted separately
---so a single edit which changed title, body and tags will count as three edits
  SUM(CASE WHEN (ph.Id<c.cwid) THEN 1 ELSE 0 END) AS [Edits before CW],
  SUM(CASE WHEN (ph.Id>c.cwid) THEN 1 ELSE 0 END) AS [Edits after CW],
  ph.PostId AS [Post Link],
  revisions = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions'
FROM cw_cte c INNER JOIN PostHistory ph ON ( 
WHERE (ph.PostHistoryTypeId in (1,2,3,4,5,6,7,8,9))
---only edits
GROUP BY ph.PostId

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results