8

Do we have any query to know list of users sorted by total number of answers/questions/posts they contributed on Hinduism SE?

10

The following query will show users, total posts, questions, answers, tag excerpts and tag wikis.

select u.id [User Link]
     , u.reputation
     , u.creationdate
     , count(*) [# posts]
     , sum(case when posttypeid = 1 then 1 else null end) [# Questions]
     , sum(case when posttypeid = 2 then 1 else null end) [# Answers]
     , sum(case when posttypeid = 4 then 1 else null end) [# Excerpts]
     , sum(case when posttypeid = 5 then 1 else null end) [# Wiki]
from users u
inner join posts p on p.owneruserid = u.id
where posttypeid in (1,2,4,5)
group by u.id
       , u.reputation
     , u.creationdate
order by [# posts] desc

This is what the result looks like today: SEDE result with users, total post counts

Please note that this only counts undeleted posts. For deleted posts we lack the owneruserid. See Can some metadata about deleted posts be included in Data.SE? for what data is available for deleted posts.

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

3
  • This is what i wanted.. thanks ! – YDS Jun 16 at 14:00
  • bdw, is it counting deleted posts too? – YDS Jun 16 at 14:08
  • @YDS no, it is not because on the PostsWithDeleted table the owneruserid is null-ed for deleted posts: meta.stackexchange.com/a/266431/158100 – rene Jun 16 at 14:30

You must log in to answer this question.

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