1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

WITH AScore AS (
  SELECT ParentId AS Id, MAX(Score) AS Score
  FROM Posts
  WHERE
    ParentId IS NOT NULL
  GROUP BY ParentId
),
Raw AS (
  SELECT TOP 100
    A.TagId AS TagA,
    B.TagId AS TagB,
    C.TagId AS TagC,
    AVG(1.0 *
      CASE
        WHEN AScore.Score > ##MaxPostScore:int?10##
        THEN ##MaxPostScore##
        ELSE AScore.Score
      END) AS Score,
    COUNT(*) AS Count
  FROM PostTags A, PostTags B, PostTags C, AScore
  WHERE
    AScore.Id = A.PostId
    AND
    AScore.Id = B.PostId
    AND
    AScore.Id = C.PostId
    AND
    A.TagId < B.TagId
    AND
    B.TagId < C.TagId
  GROUP BY
    A.TagId,
    B.TagId,
    C.TagId
  HAVING COUNT(*) > ##MinTagCount:int?100##
  ORDER BY Score DESC
)
SELECT
  A.TagName,
  B.TagName,
  C.TagName,
  Raw.Score,
  Raw.Count
FROM Raw, Tags A, Tags B, Tags C
WHERE
  Raw.TagA = A.Id
  AND
  Raw.TagB = B.Id
  AND
  Raw.TagC = C.Id
ORDER BY Score DESC, A.Id, B.Id, C.Id

fork query

edited feb 26

created feb 26 peterh - PLEASE DELETE THIS ACCOUNT