Retagging suggestions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

WITH TagCorr AS (
  SELECT
    SrcPostTags.TagId AS SrcTagId,
    DstPostTags.TagId AS DstTagId,
    1.0 * COUNT(*) / MIN(Tags.Count) AS P
  FROM PostTags SrcPostTags, PostTags DstPostTags, Tags
  WHERE
    SrcPostTags.PostId = DstPostTags.PostId
    AND
    Tags.Id = SrcPostTags.TagId
  GROUP BY SrcPostTags.TagId, DstPostTags.TagId
),
EstPostTagsRaw AS (
  SELECT
    PostTags.PostId,
    TagCorr.DstTagId,
    SUM(LOG((TagCorr.P + ##c:float?0.1##) / (1 + ##c## - TagCorr.P)))
      AS DeNormP
  FROM PostTags, TagCorr
  WHERE
    PostTags.TagId = TagCorr.SrcTagId
  GROUP BY PostTags.PostId, TagCorr.DstTagId
),
EstPostTagsRaw2 AS (
  SELECT
    PostId,
    DstTagId,
    DeNormP,
    RANK() OVER (PARTITION BY PostId ORDER BY DeNormP DESC, DstTagId)
      AS Rank
  FROM EstPostTagsRaw
),
EstPostTags AS (
  SELECT
    PostId,
    DstTagId AS TagId,
    (1.0 + 2 * ##c##) / (1 + EXP(-DeNormP)) AS P
  FROM EstPostTagsRaw2
  WHERE
    Rank <= 5
),
Raw AS (
  SELECT
    PostId,
    TagId,
    CASE
      WHEN P < 0 THEN 0
      WHEN P > 1 THEN 1
      ELSE P
    END AS PShouldHave,
    0 AS PHas
  FROM EstPostTags

  UNION

  SELECT
    PostId,
    TagId,
    0 AS PShouldHave,
    1 AS PHas
  FROM PostTags
),
Raw2 AS (
  SELECT
    PostId,
    TagId,
    SUM(PShouldHave) AS PShouldHave,
    SUM(PHas) AS PHas
  FROM Raw
  GROUP BY PostId, TagId
),
SqDiff AS (
  SELECT
    PostId,
    SUM((PShouldHave-PHas)*(PShouldHave-PHas)) AS Diff
  FROM Raw2
  GROUP BY PostId
),
Raw3 AS (
  SELECT
    Raw2.PostId,
    Raw2.TagId,
    Tags.TagName,
    Tags.Count AS TagCount,
    Raw2.PShouldHave,
    Raw2.PHas,
    ABS(PShouldHave-PHas) AS PDiff,
    SqDiff.Diff AS SqDiff,
    SqDiff.Diff / (1 + SqDiff.Diff) AS SqDiffNorm,
    MAX(ABS(PShouldHave-PHas) * LOG(Tags.Count))
      OVER (PARTITION BY Raw2.PostId) AS MaxPDiffTagCount
  FROM Raw2, SqDiff, Tags
  WHERE
    Raw2.PostId = SqDiff.PostId
    AND
    Tags.Id = Raw2.TagId
)
SELECT
  PostId AS [Post Link],
  TagName,
  PostId,
  TagId,
  TagCount,
  PShouldHave,
  PHas,
  PDiff,
  SqDiff,
  SqDiffNorm,
  MaxPDiffTagCount
FROM Raw3
ORDER BY
  (1 - SqDiffNorm) * MaxPDiffTagCount / (1 + MaxPDiffTagCount) DESC,
  PostId DESC,
  ABS(PShouldHave-PHas) DESC,
  TagCount DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results