Return to Question

deleted 15 characters in body; edited title

Source Link

edited May 11, 2019 at 18:11

255.6k
61
638
1.3k

Retrieve list of top 100 userusers on stackexchangeStack Overflow sorted by posted content?

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

Suppose there are two users .:

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

List 
User A
User B

So far I have written querya query to list the top 100 users withwith the most postposts ( answersanswers 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 It shows:

2. BalusC (Post 17035 post)

3. Darin Dimitrov (post 21486)

The one with more postposts is at the third rank. I I could not figure out what is wrong?

But I, and could not write a correct query for it?.

I have referedreferred to these postposts:

Retrieve list of top 100 user on stackexchange sorted by posted content?

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

Suppose there are two users .

User 'A' with 5608 post (Answers + questions) And User 'B' with 2627 post(Answers + question). Query results

List 
User A
User B

So far I have written query to list top 100 users with most post( 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 sorting results incorrectly. It shows

2. BalusC (Post 17035 post)

3. Darin Dimitrov (post 21486)

The one with more post is at third rank. I could not figure out what is wrong?

But I could not write correct query for it?

I have refered to these post

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

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: