0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for enthusiasts and power users of the Android operating system

WITH
  PostTypeCounts AS
  (
    SELECT
      P.PostTypeId PostTypeId,
      count(*) PostTypeCount
    FROM
      Posts P
    GROUP BY
      P.PostTypeId
  ),
  TagCounts AS
  (
    SELECT TOP 100 --modify manually, if you like
      T.TagName Tag,
      count(*) TagCount
    FROM Posts P
      INNER JOIN PostTags PT ON PT.PostId = P.Id
      INNER JOIN Tags T ON T.Id = PT.TagId
    WHERE
      P.PostTypeId = 1 -- only questions
      AND T.TagName != 'untagged'
    GROUP BY
      T.TagName
    ORDER BY
      TagCount DESC
  ),
  TagPairCounts AS
  (
    SELECT
      T1.TagName Tag1,
      T2.TagName Tag2,
      count(*) TagPairCount
    FROM Posts P
      INNER JOIN PostTags PT1 ON PT1.PostId = P.Id
      INNER JOIN PostTags PT2 ON PT2.PostId = P.Id 
      INNER JOIN Tags T1 ON T1.Id = PT1.TagId 
      INNER JOIN Tags T2 ON T2.Id = PT2.TagId
    WHERE
      T1.TagName < T2.TagName
    GROUP BY
      T1.TagName,
      T2.TagName
    --ORDER BY
    --    TagPairCount DESC
    )
SELECT --TOP 100
  TPC.Tag1 Tag1,
  TPC.Tag2 Tag2,
  TC1.TagCount Count1,
  TC2.TagCount Count2,
  TPC.TagPairCount Count12
FROM TagPairCounts TPC 
  INNER JOIN TagCounts TC1 ON TC1.Tag = TPC.Tag1
  INNER JOIN TagCounts TC2 ON TC2.Tag = TPC.Tag2
  INNER JOIN PostTypeCounts PTC ON PTC.PostTypeId = 1
ORDER BY
  Count1 DESC
--  CAST(TPC.TagPairCount as FLOAT)
--  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) DESC

view current version

edited jan 22 13 gene

created nov 18 12 narain