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