3
$\begingroup$

I was fairly active on MO at the start. Then, as is the case with some early users, my participation became much more sporadic over the years. Recently, I realized that quite a few of the old accounts have been deleted for various reasons. For some of those, I want to keep a list of their posts so I can read, or refer to the younger generation.

My question is then: how to find the list of, say deleted accounts that have at least 5000 reputations or 50 posts at the point of deletion?

(I know how to search for posts from a particular userid, thanks to this question. But I don't know how to find all previously highly active deleted accounts, and I have no idea how to find the userid of someone I used to know but have disappeared).

$\endgroup$

1 Answer 1

8
$\begingroup$

It is rather difficult to calculate the reputation exactly from SEDE1, but the total score (i.e., the sum of the scores of all user's posts) can serve as a reasonable proxy.

Here is a query which looks at such users: Deleted accounts above the given score and number of posts.2 Notice that you can change the parameters minscore and minposts - the query returns users who have both score and number of posts $\ge$ the given values. For example, you can set on of them, or even both, to zero - to remove the restriction. (As you have already mentioned, once you have userid, you can find more about that user, such as the list of their posts: Is there a way to check all questions/answers posted from a deleted account. I will add that sometimes you might be able to find also a username that the user had before the deletion - the methods mentioned here work also for deleted users: Recognizing identifiable users changing user names.)

You can make a similar query which lists both existing and deleted users: Users above the given score and number of posts. Of course, if we're making a query combining both deleted and existing users, we can include also some data from the table Users for the ones which are not deleted, like in this query.

I can also imagine, that somebody might be interested in finding active users in some specific area rather than on MO in general. This query might possibly help with that: Deleted accounts above the given score and number of posts in a specific tag. (Again, the query has a parameter tagname - which you can change to the tag of your choice.)


Re: I have no idea how to find the userid of someone I used to know but have disappeared. I will also add that if you have a post or a comment from a deleted user, you can see the userid there. You will find userxyz - where xyz is the userid - in places where you see username for the accounts which are not deleted.

If you remember the username and you want the userid, you might simply try to search where the username was mentioned on MO - which might lead you to some of their posts or comments. Or you can search for comments mentioning this username - and you might be lucky enough that you'll find a comment responding to a comment from the delete user. Examples of such queries are here and here.


1Reputation is present in the Users table - but the users who were deleted are no longer present there. Even for users who aren't deleted, it is rather difficult to use the other data to get something which matches the reputation. Some discussions related to calculating reputation from SEDE: How to retrieve exact reputation on "legendary day"?, Reputation History from Data Explorer, A query, days hitting rep cap vs days a member (rep), Determining user's reputation as of particular date?

2Maybe you noticed the apparent discrepancy that for some of the users the column "Posts" shows a bigger number than the number of answers and questions together. The reason is that the table Posts in the database also includes the tag-wikis that the user created. (The details about this table can be found in the Database schema documentation for the public data dump and SEDE.) Here is a query which also includes tag-wikis.

$\endgroup$
1
  • 2
    $\begingroup$ This is fantastic, thank you very much! $\endgroup$ Sep 26, 2021 at 21:53

You must log in to answer this question.

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