0

Please login or register to vote for this query.

(click on this box to dismiss)

Counts questions with the most frequently used two-tag combinations.

Q&A for programmers

-- Most Frequent Two-Tag Combinations
-- Counts questions with the most frequently used two-tag combinations.

DECLARE @topCombos int = 75

DECLARE @tagCombos table
(
  Id int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Tag1Id int NOT NULL,
  Tag2Id int NOT NULL
)

;WITH topTags AS
(
  /* Do not put this number higher than 100 when running against SO */
  SELECT TOP 100 t.Id FROM Tags t ORDER BY t.Count DESC
)
INSERT INTO @tagCombos (Tag1Id, Tag2Id)
  SELECT
    tt1.Id,
    tt2.Id
    FROM topTags tt1
    CROSS JOIN topTags tt2
    WHERE tt1.Id != tt2.Id

-- Remove duplicate combinations (i.e., (X, Y) is the same as (Y, X))
DELETE FROM tc1
  FROM @tagCombos tc1
  INNER JOIN @tagCombos tc2 ON
    (tc1.Tag1Id = tc2.Tag2Id) AND
    (tc1.Tag2Id = tc2.Tag1Id) AND
    (tc1.Id < tc2.Id)

SELECT TOP (@topCombos)
  t1.TagName,
  t2.TagName,
  a.Count
  FROM
  (
    SELECT
      tc.Tag1Id,
      tc.Tag2Id,
      COUNT(1) AS Count
      FROM @tagCombos tc
      INNER JOIN PostTags pt1 ON pt1.TagId = tc.Tag1Id
      INNER JOIN PostTags pt2 ON (pt2.PostId = pt1.PostId) AND (pt2.TagId = tc.Tag2Id)
      GROUP BY
        tc.Tag1Id,
        tc.Tag2Id
  ) a
  INNER JOIN Tags t1 ON t1.Id = a.Tag1Id
  INNER JOIN Tags t2 ON t2.Id = a.Tag2Id
  ORDER BY Count DESC

fork query

created jun 5 11 Jon Seigel