Questions where a given tag was added as a suggested edit and later removed

0

Please login or register to vote for this query.

(click on this box to dismiss)

Find posts where a given tag was added through a suggested edit and has now been removed.

Craft CMS Meta

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##' + '>%')

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results