Find posts where a given tag was added through a suggested edit and has now been removed.
Q&A about the site for administrators, end users, developers and designers for Craft CMS
-- derived from http://data.stackexchange.com/stackoverflow/query/59918/questions-where-a-given-tag-was-added-not-by-its-author SELECT TOP 100 q.Id AS [Post Link], h.Text AS [Edited Tags], 'site://suggested-edits/' + Cast(se.Id AS VARCHAR(10)) AS [Link], q.OwnerUserId AS [User Link], -- author q.CreationDate AS [Post Date], h.UserId AS [User Link], -- editor h.CreationDate AS [Edit Date] FROM PostHistory h, Posts q, SuggestedEdits se WHERE h.PostId = q.Id AND h.PostHistoryTypeId = 6 -- tag edit AND h.Text LIKE '%<' + '##Tag##' + '>%' AND h.UserId <> q.OwnerUserId AND se.PostId = h.PostId AND se.ApprovalDate = h.CreationDate -- approximate check that se is the suggested edit that led to revision h -- note that se.RevisionGUID is the revision before the suggested edit, whereas h.RevisionGUID is the revision after the edit AND q.Tags NOT LIKE '%<' + '##Tag##' + '>%' AND NOT EXISTS (SELECT Id FROM PostHistory g WHERE g.PostId = h.PostId AND (g.PostHistoryTypeId = 3 OR g.PostHistoryTypeId = 6) AND g.CreationDate < h.CreationDate AND g.Text LIKE '%<' + '##Tag##' + '>%')