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

WITH
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
),
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
)
SELECT TOP 10
    TPC.Tag1
FROM
    TagPairCounts TPC

Enter Parameters

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