2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

SELECT TOP 3 *
FROM Posts
WHERE PostTypeId = 1

SELECT TOP 200
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [Rank], 
    Id, 
    Reputation,
    Location
INTO #TopUsers
FROM Users
WHERE
    LOWER(Location) LIKE '%vietnam%'
    OR LOWER(Location) LIKE '%viet nam%' COLLATE Latin1_general_CI_AI
    OR UPPER(Location) LIKE '%VN'
ORDER BY Reputation DESC

SELECT
  u.Id,
  u.Reputation,
  t.TagName,
  SUM(IIF(v.VoteTypeId = 2, 1, -1)) TagScore
INTO #TopUsersWithTagScore
FROM #TopUsers u
JOIN Posts     a  ON a.OwnerUserId = u.Id
JOIN Posts     q  ON q.Id = a.ParentID
JOIN PostTags pt  ON pt.PostId = q.Id
JOIN Tags      t  ON pt.TagId = t.Id
JOIN Votes     v  ON v.PostId = a.Id AND v.VoteTypeId IN (2, 3)
GROUP BY t.TagName, u.Id, u.Reputation

;

WITH WithRank AS (
SELECT
  Id,
  Reputation,
  TagName,
  TagScore,
  ROW_NUMBER() OVER(PARTITION BY Id ORDER BY TagScore DESC) [TagRank]
FROM #TopUsersWithTagScore u
),
WithRank3 AS (
SELECT * FROM WithRank
WHERE TagRank <= 3
)

SELECT
  Id [User Link],
  Reputation,
  TopTags = STUFF((
    SELECT ', <' + TagName + '>'
    FROM WithRank3
    WHERE WithRank3.Id = this.Id
    FOR XML PATH ('')
  ), 1, 2, '')
FROM WithRank3 this
GROUP BY Id, Reputation
ORDER BY Reputation DESC

view current version

edited oct 28 21 NearHuscarl

created mar 11 16 anonymous