Another possible suggestable tag synonyms by you

1

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

DECLARE @UserId INT;

SELECT @UserId = Id FROM Users WHERE AccountId = ##SEAccountId:INT##;

DECLARE @T BIGINT;

SELECT @T = COUNT(*) FROM Posts WHERE PostTypeId = 1;

WITH AB AS (
  SELECT
    A.TagId AS TagIdA,
    B.TagId AS TagIdB,
    CAST(COUNT(*) AS FLOAT) AS C
  FROM PostTags A, PostTags B
  WHERE
    A.PostId = B.PostId
    AND
    A.TagId <> B.TagId
  GROUP BY A.TagId, B.TagId
),
Raw AS (
  SELECT
    Id,
    TagName,
    CAST(Count AS FLOAT) AS C
  FROM Tags
  WHERE
    Count > 0
    AND
    Count < @T
)
SELECT TOP 100
  A.TagName,
  A.C AS CA,
  B.TagName,
  B.C AS CB,
  AB.C AS CAB,
  --(AB.P-A.P*B.P)/SQRT(A.P*(1-A.P)*B.P*(1-B.P)) AS Corr
  1.0 * (AB.C * @T - 1.0*A.C*B.C) /
  SQRT(A.C*(@T-A.C)*B.C*(@T-B.C)) AS Corr
FROM Raw A, Raw B, AB
WHERE
  A.Id = AB.TagIdA
  AND
  B.Id = AB.TagIdB
  AND
  A.Id IN (
    SELECT PostTags.TagId
    FROM Posts, Votes, PostTags
    WHERE
      Votes.PostId = Posts.Id
      AND
      Posts.CommunityOwnedDate IS NULL
      AND
      Posts.PostTypeId = 2
      AND
      Votes.VoteTypeId BETWEEN 2 AND 3
      AND
      Posts.OwnerUserId = @UserId
      AND
      PostTags.PostId = Posts.ParentId
    GROUP BY PostTags.TagId
    HAVING SUM(5 - 2 * Votes.VoteTypeId) >= 5
  )
  /*
  AND
  A.C < B.C
  AND
  A.C > ##SmallerTagMin:int?100##
  AND
  B.C > ##LargerTagMin:int?100##
  */
ORDER BY Corr DESC

Enter Parameters

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