2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

; with nextedit_cte as
(SELECT TOP ##max?10000##
---change parameter max to get less results - if the query runs for two long
  ph1.Id AS phid,
  MIN(ph2.Id) AS nextid
FROM PostHistory ph1 INNER JOIN PostHistory ph2 ON ph1.PostId=ph2.PostId
WHERE (ph1.PostHistoryTypeId IN (2,5,8)) AND (ph2.PostHistoryTypeId IN (5,8)) AND (ph1.Id<ph2.Id)
---5 = Edit Body - modified post body (raw markdown)
GROUP BY ph1.Id
ORDER BY ph1.Id DESC
), difflength_cte AS (
SELECT
  ph1.PostId AS pid,
  ph2.Id AS phid,
  LEN(ph1.Text) AS oldlength, 
  LEN(ph2.Text) AS newlenght,
  CAST((LEN(ph2.Text)-LEN(ph1.Text)) AS int) dif, 
  CAST(ABS(LEN(ph2.Text)-LEN(ph1.Text)) AS int) absdif
FROM PostHistory ph1 INNER JOIN nextedit_cte ne ON ph1.Id=ne.phid
  INNER JOIN PostHistory ph2 ON ph2.Id=ne.nextid
)
 
SELECT TOP(##num?500##)
  ph.PostId AS [Post Link],
  concat('site://posts/',
          ph.PostId,
          '/revisions'
          ) [Revisions Link],
  dl.oldlength AS [Old Length], 
  dl.newlenght AS [New Length],
  dl.absdif AS [Difference],
  ph.Comment, ph.CreationDate,
  ph.UserId AS [User Link]
FROM PostHistory ph INNER JOIN difflength_cte dl ON ph.Id=dl.phid
WHERE (dl.absdif>=##min?500##)
---this parameter sets how many characters
ORDER BY ph.CreationDate DESC

fork query

edited jun 16

created jun 16 Martin20528