Doomed tag suggestions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT ts.SourceTagName, ts.TargetTagName, ts.Score, voters.numVoters, sourceQ.numQ AS sourceQs, tgtQ.numQ AS tgtQs
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 AND
  (voters.numVoters IS NULL OR voters.numVoters < 5)
ORDER BY ts.Score DESC, voters.numVoters DESC;

Enter Parameters

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