Publishers of rare secrets

1

Please login or register to vote for this query.

(click on this box to dismiss)

Users having the most score, weighted by the rareness of their tags

Computational Science

Q&A for scientists using computers to solve scientific problems

WITH UserTagScore AS (
  SELECT A.OwnerUserId AS UserId, PostTags.TagId, SUM(A.Score) AS Score
  FROM Posts Q, Posts A, PostTags
  WHERE
    A.OwnerUserId > 0
    AND
    A.ParentId = Q.Id
    AND
    Q.Id = PostTags.PostId
  GROUP BY A.OwnerUserId, PostTags.TagId
  HAVING SUM(A.Score) > 0
),
UserSumScore AS (
  SELECT UserId, SUM(Score) AS Score
  FROM UserTagScore
  GROUP BY UserId
)
/*
SELECT TOP 100
  UserTagScore.UserId AS [User Link],
  SUM(1.0 * UserTagScore.Score / Tags.Count) / MIN(UserSumScore.Score)
    AS WeightedScore
FROM UserTagScore, UserSumScore, Tags
WHERE
  UserTagScore.TagId = Tags.Id
  AND
  UserTagScore.Score > 0
  AND
  UserTagScore.UserId = UserSumScore.UserId
GROUP BY UserTagScore.UserId
ORDER BY WeightedScore DESC, UserTagScore.UserId;
*/
SELECT TOP 100
  UserTagScore.UserId AS [User Link],
  SUM(1.0 * UserTagScore.Score / Tags.Count) AS WeightedScore
FROM UserTagScore, Tags
WHERE
  UserTagScore.TagId = Tags.Id
  AND
  UserTagScore.Score > 0
GROUP BY UserTagScore.UserId
ORDER BY WeightedScore DESC, UserTagScore.UserId

Enter Parameters

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