Info about pending tag synonyms

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 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 voters.numVoters DESC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results