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

2

(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

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)

-- OERatio = 1 is for independent tags;
-- more means there is a positive correlations between them
-- a typical cut-off can be OERatio >= 4
-- to be set manually

-- also, if enstead of 256 tags (good for StackOverflow)
-- you want less (e.g. 64 or 128) - set it

WITH
PostTypeCounts AS
(
SELECT
P.PostTypeId PostTypeId,
count(*) PostTypeCount
FROM
Posts P
GROUP BY
P.PostTypeId
),
TagCounts AS
(
SELECT TOP 256 --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
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,
CAST((TPC.TagPairCount * PTC.PostTypeCount) as FLOAT)
/ CAST((TC1.TagCount * TC2.TagCount) AS FLOAT)
OERatio --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
INNER JOIN PostTypeCounts PTC ON PTC.PostTypeId = 1
WHERE
CAST((TPC.TagPairCount * PTC.PostTypeCount) as FLOAT)
/ CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) >= 4
ORDER BY
OERatio DESC
--  CAST(TPC.TagPairCount as FLOAT)
--  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) DESC

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached