When querying the live data using Data Explorer for a weekly count of unanswered, unclosed questions tagged with android, there seems to be a large discrepancy between these results and those using the March 16th data dump from the archive.

Using Data Explorer with the above query, we see the following graph: Data Explorer graph

Using the archive data (as imported to a local PostgreSQL database) with the query:

select extract(week from creationdate), count(*) 
    from posts q 
    not exists (select * from posts a where a.parentid = q.id) 
    and q.creationdate >= '2014-01-01' 
    and q.creationdate < '2015-01-01' 
    and q.closeddate is null
    and q.parentid is null
    and q.tags like '%<android>%'
group by extract(week from creationdate)

we see: Local March 16 data dump graph

Slight deviations between the two data sources are absolutely expected due to the dynamic nature of Stack Overflow (questions deleted, answered later on, closed, etc). However, it seems unlikely that such a large discrepancy between the two data sources would exist especially since the large spike in unanswered questions appears in both data sets, but at different times.

My questions are:

  1. Can another user with a locally imported Stack Overflow March 16th data dump confirm this phenomenon to rule out the possibility of an error on my side (which is absolutely possible)?
  2. Was there some type of systematic change in Stack Overflow that could have caused this discrepancy between data sets?
  • Tangential: not exists (select * from Posts a where a.ParentId = q.Id) is equivalent to q.AnswerCount = 0, and the latter ought to be faster. – user259867 Aug 2 '15 at 5:18

This is the culprit. The bot deletes zero-score unanswered questions (with fewer than 2 comments) after 365 days. The spike that you observe is not a spike in questions posted, but a reverted wave of questions deleted. In the March snapshot, this wave of deletion is in March 2014 (around week 10). In the current snapshot, it is in July 2014.

Removal of abandoned questions after 365 days is the 3rd most common deletion reason on Stack Overflow, trailing only the deletion of questions and answers by their authors.

  • Ahhh thank you so much! This has been bugging me all day as I'm attempting to do analysis on this particular class of questions. It seems now that this analysis may now be limited to questions prior to March 2014 (around week 10). – David Aug 2 '15 at 5:30

You must log in to answer this question.

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