3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for system and network administrators

WITH #SingleUseTags AS (SELECT TagName, PostId, Tags
  FROM Tags AS t
  INNER JOIN PostTags AS pt
    ON pt.TagId = t.Id
  INNER JOIN Posts AS p
    ON pt.PostId = p.Id
  LEFT OUTER JOIN TagSynonyms
    ON TagName = SourceTagName
  WHERE [Count] = 1
    AND ExcerptPostId IS NULL
    AND WikiPostId IS NULL
    AND SourceTagName IS NULL)
SELECT TagName, DATEADD(MONTH,
  IIF(PATINDEX('%.Meta', DB_NAME()) = 0, 6, 12), -- cf. https://meta.stackexchange.com/a/341923/295232
  ca.CreationDate) AS 'Will be deleted after',
  PostId AS [Post Link], Tags
  FROM #SingleUseTags AS sut
  CROSS APPLY (
    SELECT MIN(CreationDate) AS CreationDate
      FROM PostHistory
      WHERE PostHistoryTypeId IN (3, 6)
        AND PostId = sut.PostId
        AND Text LIKE '%<' + TagName + '>%'
  ) ca
  ORDER BY 2

fork query

edited sep 22 21

created aug 19 21 Glorfindel