Retrieve list of top 100 users on Stack Overflow sorted by posted content?

Question

I am trying to replicate the top user page with the users sorted by number of posts.

Suppose there are two users:

User 'A' with 5608 post (Answers + questions) And User 'B' with 2627 post(Answers + question). The query results should be:

List 
User A
User B

So far I have written a query to list the top 100 users with the most posts (answers and questions). The query is below:

Select Top 100 
       OwnerUserId as [User Link]
       , sum(score) score
       , count(*) [# posts]
       , sum(case posttypeid when 1 then score else 0 end) [question score]
       , sum(case posttypeid when 2 then score else 0 end) [answer score]
       , sum(case posttypeid when 1 then 1 else 0 end) [# questions]
       , sum(case posttypeid when 2 then 1 else 0 end) [# answers]
from Posts p
inner join posttags pt on pt.postid = coalesce(p.parentid, p.id)

WHERE 
 owneruserid is not null
and posttypeid in (1,2) -- only questions and answers
and communityowneddate is null
group by owneruserid    
order by sum(score) desc

This query is sorting results incorrectly. It shows:

2. BalusC (Post 17035 post)

3. Darin Dimitrov (post 21486)

The one with more posts is at the third rank. I could not figure out what is wrong, and could not write a correct query for it.

I have referred to these posts:

• Retrieve list of top user tags on data.stackexchange.com

• Incorrect syntax near the keyword 'where'

Answer 1

First of all, the join with PostTags causes posts to count multiple times, depending on how many tags they have.

Also, you're ordering by sum(score) desc - that's the total score of the posts. If you want to order by the # of posts, just order by count(*) desc, like this query.