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), (1370722) DECLARE @findstr nvarchar(max) = '"Id":' SELECT ph.Id, pht.Name, ph.PostId AS [Post Link], ph.CreationDate, ph.UserId AS [User Link], 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) -- Exclude editing-related things (1~9), deletion (12, 13), locking (14, 15), protection (19, 20) AND ph.PostHistoryTypeId NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 19, 20) -- Exclude moderator actions and standard Community actions AND ( ph.UserId NOT IN (SELECT UserId FROM #mods) OR ( ph.UserId = -1 AND ph.Text != '{"Voters":[{"Id":-1,"DisplayName":"Community"}]}' AND CAST(SUBSTRING(ph.Text, CHARINDEX(@findstr, ph.Text) + LEN(@findstr), CHARINDEX(',', ph.Text, CHARINDEX(@findstr, ph.Text) + LEN(@findstr)) - (CHARINDEX(@findstr, ph.Text) + LEN(@findstr))) AS int) NOT IN (SELECT UserId FROM #mods) ) ) -- Before implementation of the dupehammer, for simplicity AND ph.CreationDate < '2014-05-01' ORDER BY ph.UserId DESC, ph.CreationDate DESC