Users whose top 10 tag scores are all different, by increasing order of reputation

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

CREATE TABLE #TagScores (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  Score INT NOT NULL,
  Rank INT NOT NULL,
  PRIMARY KEY (UserId, TagId)
);

WITH Raw AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    PostTags.TagId AS 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
)
INSERT INTO #TagScores
SELECT
  UserId,
  TagId,
  Score,
  RANK() OVER (PARTITION BY UserId ORDER BY Score DESC) AS Rank
FROM Raw
WHERE UserId IN (
  SELECT UserId
  FROM Raw
  GROUP BY UserId
  HAVING COUNT(*) >= 10
);

SELECT TOP 100
  Id AS [User Link],
  Id,
  Reputation
FROM Users
WHERE
  Reputation > 1
  AND 
  Id IN (
    SELECT
      UserId
    FROM #TagScores
    WHERE
      Rank <= 10
    GROUP BY UserId
    HAVING COUNT(DISTINCT Score) = 10
  )
ORDER BY Reputation, Id;

Enter Parameters

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