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: