statistics on the questions/answers/user accounts removal

Question

Is there some kind of statistics on the questions/answers/user accounts removal on Stack Overflow?

As a researcher I would like to consider the Stack Overflow data dump as a representation of the activity of the Stack Overflow participants. However, the fact that questions/answers/accounts can be deleted means that this representation is incomplete. While incompleteness is, as such, not a new issue on its own, it would be great to have an estimate of how incomplete is the data.

(I've checked the Community Statistics discussion from 2011 but it seems that deletion was not considered when not related to migration to other network sites.)

Answer 1

With the addition of the table postswithdeleted in SEDE we can reliable answer the question about visible and deleted posts. There is only meta-data available, like type of post and deletion date. The other fields are emptied.

For the users we can only do a guesstimate based on the assumption that the primarykey column id of the users table is a sequence that started at 1 and increases with 1 for every new user. The sequence is generated by Microsoft Sql Server and it is known that it sometimes jumps in the sequence. By subtracting the count(*) from max(id) we get an estimate how many users are deleted.

Based on above explanation I've created this query to give you the rough numbers for removed data from the dataset:

select case when posttypeid = 1 then 'Question' else 'Answer'end as [Stat type]
     , sum(case when deletiondate is null then 1 else 0 end) [not deleted] 
     , sum(case when deletiondate is null then 0 else 1 end) [deleted] 
     , count(*) [total]
from postswithdeleted
where posttypeid in (1,2)
group by posttypeid
union 
select 'Users '
   , count(*) [# of users]
   , max(id) - count(*) [est. # of deleted users]
   , null
from users

When run today the results of above query are:

Keep in mind SEDE is only updated once a week, on Sunday.