0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #mods (UserId int)
INSERT INTO #mods SELECT UserId FROM Badges WHERE Name IN ('Sheriff', 'Constable')
INSERT INTO #mods VALUES (-1)
-- For Stack Overflow
INSERT INTO #mods VALUES 
(1), (2), (3), (4), 
(51),
(811), 
(1438), (1583), (3279), (6212), (7028), 
(10936), (13249), (17174), (23354), (50049),(70613), (80572), (91687), (98786),
(105971), (115049), (115866), (122607), (178082), (256196), (274402), (347414), (426671), (472021), (716216), (850152)

DECLARE @findstr nvarchar(max) = '"Id":'

SELECT 
  ph.Id, 
  pht.Name,
  ph.PostId AS [Post Link], 
  ph.CreationDate, 
  ph.UserId AS [User Link], 
  ph.Comment, 
  ph.Text 
FROM 
  PostHistory AS ph
  LEFT OUTER JOIN Posts AS p
    ON ph.PostId = p.Id
  LEFT OUTER JOIN PostHistoryTypes AS pht
    ON ph.PostHistoryTypeId = pht.Id
WHERE
  LEN(ph.Text) - LEN(REPLACE(ph.Text, @findstr, '')) = 1*LEN(@findstr)
  -- Skip editing-related things
  AND ph.PostHistoryTypeId NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
  -- Exclude protection
  AND ph.PostHistoryTypeId NOT IN (19, 20)
  -- Exclude self-deletion
  AND NOT (ph.PostHistoryTypeId IN (12, 13) AND p.OwnerUserId = ph.UserId)
  -- Exclude moderator actions
  AND ph.UserId NOT IN (SELECT UserId FROM #mods)
  AND ph.CreationDate >= '2008-01-01'
  AND ph.CreationDate <  '2014-05-01' -- before implementation of the dupehammer
ORDER BY
  ph.UserId DESC,
  ph.CreationDate DESC
  
  
-- 12 = Post Deleted
-- 13 = Post Undeleted
-- 19 = Question Protected
-- 20 = Question Unprotected

view current version

edited jan 2 15

created jan 2 15 senshin