2

Please login or register to vote for this query.

(click on this box to dismiss)

1=Question, 2=Answer, 3=Wiki (seems to be edits on tag wikis and tag wiki excerpts), 4=TagWikiExcerpt, 5=TagWiki, 6=ModeratorNomination, 7=WikiPlaceholder, 8=PrivilegeWiki

Q&A for amateur radio enthusiasts

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

DECLARE @num_posts int =
    (SELECT COUNT(DISTINCT p.Id) AS TOTAL
     FROM Posts p
     WHERE p.PostTypeId in (##EditTypes?3, 4, 5, 6, 7, 8##))

-- 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 ##TopX:int?100##
  row_number() over
    (ORDER BY count(distinct ph.PostId) desc, ph.UserDisplayName,
     u.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, u.DisplayName) AS [User Link],
  COUNT(DISTINCT ph.PostId) AS [Edits],
  round(100.0*count(distinct ph.PostId)/@num_posts, 2) as [Percentage of posts]
INTO #Editors
FROM
  PostHistory ph
  LEFT JOIN Posts p ON ph.PostId = p.Id
  LEFT JOIN Users u ON ph.UserId = u.Id
WHERE
  p.PostTypeId in (##EditTypes##)
  AND LEN(RTRIM(ph.Text)) > 0
GROUP BY
  ph.UserId, ph.UserDisplayName, u.DisplayName
HAVING COUNT(DISTINCT ph.PostId) >= ##MinEdits?1##

SELECT [User Link], Edits, [Percentage of Posts]
FROM #Editors
ORDER BY RowNum

SELECT RowNum, Edits as [Edits of post type ##EditTypes##]
FROM #Editors
ORDER BY RowNum

fork query

edited may 22 16

created apr 7 16 amaranth