Observed to expected ratio (o/e) for tag pairs for n most popular tags

2

Please login or register to vote for this query.

(click on this box to dismiss)

P(tag1 and tag2)/[P(tag1) P(tag2)] for 256 most popular tags, see http://bit.ly/map-of-so

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- for plots like this one: 
-- http://bit.ly/map-of-so
-- P(tag1 and tag2)/[P(tag1) P(tag2)] is
-- #tag1tag2 #questions / (#tag1 #tag2)

WITH
  PostTypeCounts AS
  (
    SELECT
      P.PostTypeId PostTypeId,
      count(*) PostTypeCount
    FROM
      Posts P
    GROUP BY
      P.PostTypeId
  ),
  TagCounts AS
  (
    SELECT TOP 256
      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
    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 10
    TPC.Tag1 Tag1,
    TPC.Tag2 Tag2,
    TPC.TagPairCount / (TC1.TagCount * TC2.TagCount) --almost there, just to normalize
FROM
    TagPairCounts TPC
      INNER JOIN TagCounts TC1 ON TC1.Tag = TPC.Tag1
      INNER JOIN TagCounts TC2 ON TC2.Tag = TPC.Tag2

Enter Parameters

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