Q&A for professional and enthusiast programmers
SELECT TOP 100 ts.SourceTagName, ts.TargetTagName, ts.Score, voters.numVoters, sourceQ.numQ AS sourceQs, tgtQ.numQ AS tgtQs, DATEDIFF(d, ts.CreationDate, (SELECT MAX(CreationDate) FROM POSTS)) AS ageDays FROM TagSynonyms ts LEFT OUTER JOIN (SELECT tagScores.tn AS tgt, COUNT(*) AS numVoters FROM (SELECT target.tn, a.OwnerUserId, SUM(a.Score) AS scoreSum FROM (SELECT DISTINCT(TargetTagName) AS tn FROM TagSynonyms WHERE ApprovalDate is NULL ) AS target INNER JOIN Tags AS t ON target.tn=t.TagName INNER JOIN PostTags AS pt ON t.Id=pt.TagId INNER JOIN Posts AS q ON pt.PostId=q.Id INNER JOIN Posts AS a ON q.Id=a.ParentId GROUP BY target.tn, a.OwnerUserId ) AS tagScores WHERE tagScores.scoreSum >= 5 GROUP BY tagScores.tn ) AS voters ON ts.TargetTagName=voters.tgt LEFT OUTER JOIN (SELECT tgs.TagName, qCt.numQ FROM (SELECT TagId, COUNT(*) AS numQ FROM PostTags GROUP BY TagId) AS qCt INNER JOIN Tags AS tgs ON qCt.TagId=tgs.Id ) AS sourceQ ON ts.SourceTagName = sourceQ.TagName LEFT OUTER JOIN (SELECT tgs.TagName, qCt.numQ FROM (SELECT TagId, COUNT(*) AS numQ FROM PostTags GROUP BY TagId) AS qCt INNER JOIN Tags AS tgs ON qCt.TagId=tgs.Id ) AS tgtQ ON ts.TargetTagName = tgtQ.TagName WHERE ts.ApprovalDate IS NULL ORDER BY ts.score DESC;