Top Stackoverflow users in Vietnam

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

-- Select top users by reputation in Vietnam
SELECT TOP 2000
    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

-- get score from each tag from top users
SELECT
  Rank,
  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, Rank

;

-- rank score from each tag from top users
WITH WithRank AS (
SELECT
  Rank,
  Id,
  Reputation,
  TagName,
  TagScore,
  ROW_NUMBER() OVER(PARTITION BY Id ORDER BY TagScore DESC) [TagRank]
FROM #TopUsersWithTagScore u
),
-- get top 3 tags from top users
WithRank3 AS (
SELECT * FROM WithRank
WHERE TagRank <= 3
)

-- merge 3 tags by concating tag string
SELECT
  Rank,
  Id [User Link],
  Reputation,
  Tags = (
    SELECT '<' + TagName + '>'
    FROM WithRank3
    WHERE WithRank3.Id = this.Id
    FOR XML PATH (''), TYPE)
    -- https://stackoverflow.com/a/52819854/9449426
    .value('.', 'varchar(8000)')
FROM WithRank3 this
GROUP BY Id, Reputation, Rank
ORDER BY Rank ASC

Enter Parameters

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