instruction: Percent closed per canonical tag.
response: WITH canon AS (SELECT Tags.Id, Count, Tags.TagName AS otag, COALESCE(c.TargetTagName, b.TargetTagName, a.TargetTagName, TagName) AS tagname FROM Tags LEFT JOIN TagSynonyms AS a ON (a.SourceTagName = TagName) LEFT JOIN TagSynonyms AS b ON (b.SourceTagName = a.TargetTagName) LEFT JOIN TagSynonyms AS c ON (c.SourceTagName = b.TargetTagName)) SELECT TagName, COUNT(*) AS closed, MAX(canon.Count) AS questions, CAST(COUNT(*) AS FLOAT) / MAX(canon.Count) AS percentclosed FROM Posts AS p LEFT JOIN PostTags AS pt ON p.Id = pt.PostId LEFT JOIN canon ON pt.TagId = canon.Id WHERE PostTypeId = 1 AND NOT ClosedDate IS NULL AND canon.Count >= 1000 GROUP BY TagName ORDER BY CAST(COUNT(*) AS FLOAT) / MAX(canon.Count) DESC