2

Please login or register to vote for this query.

(click on this box to dismiss)

Rank the users who have edited the most tag-wiki entries. The default badge link IDs only work on scifi.SE.

Q&A for amateur radio enthusiasts

-- TopX: Top x editors
DECLARE @top int = ##TopX:int?100##
-- RABadge: Research Assistant badge ID "Different for each site"
DECLARE @rabadge nvarchar(6) = ##RABadge:string?80##
-- TABadge: Tag Editor badge ID "Different for each site"
DECLARE @tabadge nvarchar(6) = ##TABadge:string?53##

-- ph.UserDisplayName is always NULL when the user isn't deleted

-- Order by edits, then display name. Show deleted users at the end.
-- In the ridiculously unlikely situation that two users have the same
-- display name and the same number of tag wiki edits, order by user ID.
SELECT TOP (@top)
  row_number() over
    (ORDER BY count(distinct ph.PostId) DESC, ph.UserDisplayName,
     Users.DisplayName, ph.UserId) as RowNum,
  (CASE WHEN ph.UserId IS NOT NULL THEN
     'site://users/' + CAST(ph.UserId AS nvarchar) + '|' ELSE '' END) +
     COALESCE(ph.UserDisplayName, Users.DisplayName) AS [User Link],
  COUNT(DISTINCT ph.PostId) AS [Edits],
  -- All users returned by this query will have at least 1 tag-wiki edit
  -- and hence the Tag Editor badge
  (CASE WHEN COUNT(DISTINCT ph.PostId) >= 50 THEN
     CASE WHEN ph.UserId IS NOT NULL THEN
       'site://help/badges/' + @rabadge + '/research-assistant?userid='
       + CAST(ph.UserId AS nvarchar) + '|' ELSE ''
     END + N'★★'
     ELSE CASE WHEN ph.UserId IS NOT NULL THEN
       'site://help/badges/' + @tabadge + '/tag-editor?userid='
       + CAST(ph.UserId AS nvarchar) + '|' ELSE ''
     END + N'★'
   END) AS [Badge]
INTO #ResearchAssistants
FROM
  PostHistory AS ph
  LEFT OUTER JOIN Posts AS p ON ph.PostId = p.Id
  LEFT OUTER JOIN Users ON ph.UserId = Users.Id
WHERE
  p.PostTypeId = 5 -- TagWiki
  AND LEN(RTRIM(ph.Text)) > 0
GROUP BY
  ph.UserId, ph.UserDisplayName, Users.DisplayName

-- Show the table
SELECT [User Link], Edits, Badge
FROM #ResearchAssistants
ORDER BY RowNum

-- Make a graph
SELECT RowNum, Edits
FROM #ResearchAssistants
ORDER BY RowNum

fork query

edited apr 24 16 amaranth

created feb 19 14 rolfl