Do we have any query to know list of users sorted by total number of answers/questions/posts they contributed on Hinduism SE?
2 Answers
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:
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.
-
-
-
@YDS no, it is not because on the PostsWithDeleted table the owneruserid is null-ed for deleted posts: meta.stackexchange.com/a/266431/158100– reneCommented Jun 16, 2021 at 14:30
I could create those queries with the help of some other queries:
-
2