Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
; with tags_cte AS ( SELECT Id PostId, replace(value,'>','') TagName FROM PostsWithDeleted CROSS APPLY STRING_SPLIT(Tags,'<') Where posttypeid = 1 and Deletiondate is NOT NULL and len(value) > 0 ), tagstats_cte as ( SELECT t.TagName AS TagName, ---Count(q.DeletionDate) AS DeletedCount Count(q.Id) AS DeletedCount FROM PostsWithDeleted q INNER JOIN tags_cte t ON q.Id = t.PostId WHERE (q.PostTypeId = 1) GROUP BY t.TagName ) SELECT ts.SourceTagname, ts.TargetTagname, t.DeletedCount FROM TagSynonyms ts INNER JOIN tagstats_cte t ON ts.SourceTagname=t.Tagname ORDER BY DeletedCount DESC