3

I wanted a query like this that ranks by answer vote count for a given tag but that also includes a column with the number of answers.

Essentially a replica of the topusers page list for all-time answerers showing more than top 20.

(There's also this excellent query, but it adds TMI for the above simple use case.)

5

Here is that query.

Notice I join the tables posts, posttags and tags. In the columns I sum the score, once for all posts and once only for posts that are not community wiki. I also included the vote split in up and downvotes and the number of acceptvotes.

select rank() over(order by sum(p.score) desc
                          , sum(acceptvotes) desc
                          , sum(upvotes) desc
                          , sum(downvotes) desc) [rank]
     , p.owneruserid [User Link]
     , sum(p.score) [total score]
     , sum(acceptvotes) [accept votes]
     , sum(upvotes) [up votes]
     , sum(downvotes) [down votes]
     , sum(case when CommunityOwnedDate is null then p.score else 0 end) [score without cw]
     , count(p.id)  [answer count]
from posts p
inner join posttags pt on pt.postid = p.parentid
inner join tags t on t.id = pt.tagid
left outer join (
  select v.postid 
       , sum(case when v.votetypeid = 1 then 1 else 0 end) acceptvotes
       , sum(case when v.votetypeid = 2 then 1 else 0 end) upvotes
       , sum(case when v.votetypeid = 3 then 1 else 0 end) downvotes
  from votes v
  where v.votetypeid in (1,2,3)
  group by postid  
) votesplit  on votesplit.postid = p.id
where p.posttypeid = 2 -- Answers
and t.tagname = ##tag:string?data-explorer##
group by p.owneruserid
order by sum(p.score) desc
       , sum(acceptvotes) desc
       , sum(upvotes) desc
       , sum(downvotes) desc

when run today for the data-explorer tag this will be your result:

enter image description here

Keep in mind SEDE is updated once a week on Sunday.
Use the very useful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

| improve this answer | |
3

I've modified that query by adding

COUNT(DISTINCT(Posts.Id)) AS [Number of answers],

and making the join to the Votes table an outer join, since answers which aren't voted upon count as well. This is the result:

enter image description here

| improve this answer | |

You must log in to answer this question.

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