1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

DECLARE @LogFact TABLE (
  N INT NOT NULL,
  LogFact FLOAT NOT NULL,
  PRIMARY KEY(N)
);

DECLARE @n INT = 1
DECLARE @logSum FLOAT = 0

WHILE @n < 100000
BEGIN
  INSERT INTO @LogFact VALUES(@n, @logSum)
  SET @n = @n + 1
  SET @logSum = @logSum + LOG(@n)
END;

WITH TagScores AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    PostTags.TagId,
    SUM(Posts.Score) AS Score
  FROM Posts, PostTags
  WHERE
    Posts.ParentId = PostTags.PostId
    AND
    Posts.OwnerUserId > 0
    AND
    Posts.CommunityOwnedDate IS NULL
  GROUP BY Posts.OwnerUserId, PostTags.TagId
  HAVING SUM(Posts.Score) > 0
),
SumTagScore AS (
  SELECT UserId, SUM(Score) AS Score
  FROM TagScores
  GROUP BY UserId
)
SELECT TOP 100
  SumTagScore.UserId AS [User Link],
  (
    COALESCE(
        MIN(SumTagScoreLF.LogFact),
        LOG(2 * 3.1415927 * MIN(SumTagScore.Score)) / 2
          +
        LOG(1.0 * MIN(SumTagScore.Score) / 2.71728)
          * MIN(SumTagScore.Score)
      )
      - SUM(
        COALESCE(TagScoreLF.LogFact,
          LOG(2.0 * 3.1415927 * TagScores.Score) / 2
          +
          LOG(1.0 * TagScores.Score / 2.71728) * TagScores.Score
        )
      )
    )
    AS LogTagEntropy
FROM TagScores
LEFT JOIN SumTagScore ON TagScores.UserId = SumTagScore.UserId
LEFT JOIN @LogFact TagScoreLF ON TagScores.Score = TagScoreLF.N
LEFT JOIN @LogFact SumTagScoreLF ON SumTagScore.Score = SumTagScoreLF.N
WHERE
  TagScores.UserId = SumTagScore.UserId
GROUP BY SumTagScore.UserId
ORDER BY LogTagEntropy DESC, SumTagScore.UserId

fork query

edited may 6

created may 6 peterh - PLEASE DELETE THIS ACCOUNT