5

A while back I asked this question about writing a query which would rank users based on their number of close votes on posts related to a specific tag: Table of users ranked based on their number of close votes in a specific tag

Now, I want to get a ranking for both edits and close votes within the same query. Regarding the edits, I have this query: Users with most number of edits per tag

Select ph.UserId as [User Link], ph.UserId, Count(distinct ph.RevisionGUID) RevisionCount
From PostHistory ph
Join Posts p On ph.PostId = p.Id -- this is both Q and A
Join Posts q On q.Id = IsNull(p.ParentId, p.Id) -- this is Qs + Qs for the As above
Join PostTags pt On pt.PostId = q.Id
Join Tags t On t.Id = pt.TagId
Where ph.PostHistoryTypeId In (4, 5) -- Edit title, Edit body
  And t.TagName = '##TagName##'
  And ph.UserId > 0
  --And p.DeletionDate Is Null -- Exclude deleted posts
Group By ph.UserId
Having Count(distinct ph.RevisionGUID) >= '##EditsThreshold##'
Order By RevisionCount Desc
  1. How can I exclude edits on own posts?

  2. How can I combine the query above and the one for close voters ranking per tag to get a combined ranking for edits and close votes?

  3. Moreover, does it make sense to sort this data based on normalized totals (so the scale won't be skewed in the favor of edits or close votes)?

1 Answer 1

5

How can I exclude edits on own posts?

Add in the where clause: p.OwnerUserId <> ph.UserId

How can I combine the query above and the one for close voters ranking per tag to get a combined ranking for edits and close votes?

Add the queries as separate Common Table Expressions (CTE), then add a third one:

; with all_users as (
select distinct userid
from Join Posts p On ph.PostId = p.Id -- this is both Q and A
Join Posts q On q.Id = IsNull(p.ParentId, p.Id) -- this is Qs + Qs for the As above
Join PostTags pt On pt.PostId = q.Id
Join Tags t On t.Id = pt.TagId
Where ph.PostHistoryTypeId In (4, 5, 10) -- Edit title, Edit body, Close
  And t.TagName = '##TagName##'
),
edits as ( /* query */ ),
close as ( /* query */ )

In your final query:

Select all_users.userid
     , sum(revisioncount) revisioncount
     , sum(closecount) closecount
from all_users
left outer join edits on edits.userid = all_users.userid
left outer join close on close.userid = all_users.userid
group by all_users.userid

Moreover, does it make sense to sort this data based on normalized totals (so the scale won't be skewed in the favor of edits or close votes)?

I'm not a statistician but my guts say that makes sense. I've no idea what a sane approach would look like.

3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .