## 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 10000 --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,
CAST(TPC.TagPairCount as FLOAT) * CAST(PTC.PostTypeCount as FLOAT)
/ (CAST(TC1.TagCount as FLOAT) * CAST(TC2.TagCount as FLOAT))
OERatio, --almost there, just to normalize
TC1.TagCount Count1,
TC2.TagCount Count2,
TPC.TagPairCount Count12,
PTC.PostTypeCount CountAll
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 as FLOAT) * CAST(PTC.PostTypeCount as FLOAT)
/ (CAST(TC1.TagCount as FLOAT) * CAST(TC2.TagCount as FLOAT)) >= 2
AND TPC.TagPairCount >= 3
ORDER BY
OERatio DESC
--  CAST(TPC.TagPairCount as FLOAT)
--  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) DESC

### Enter Parameters

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