A query to get all posts that count for Archaeologist and Excavator

Question

I've been playing with data-explorer to peruse stackoverflow more and learn some t-sql.

I managed to write this query, which lists out all posts the user has edited and then shows a difference in Creation vs Activity date and Creation vs LastEdit date.

I then realized there is a more accurate way to count all edits (As opposed to checking LastEditorUserId)

After looking around I found two posts that sorta explain how the excavator badge is calculated. It suggests

if( Difference(LastActivityDate,LastEditDate) > 6) then 
    => Post Counts towards badge

But what if I have a case like this post. I edited it nearly 2 years after it was asked. But it was active 10 days ago. So the Diff(LastActivityDate,LastEditDate) is negative.

• Does that post count toward Archaeologist? (and excavator)
  • It should right? I mean the question was inactive for nearly 2 years, and I edited it.
• How would I account for this in my SQL query? (on data-explorer)

Answer 1

If it was active 10 days ago, then it doesn't count towards the badges because it hasn't been inactive for a period of 6 months. They're not for editing posts which are at least 6 months old.

Edited 100 posts that were inactive for 6 months

I think your question more brings up the point of whether the word "were" means:

1. The post was inactive for a period of 6 months somewhere in its history at the time of editing.

2. The post has been inactive for a period of 6 months at the time of editing.

The wording tends to lean towards case 1, although I think the badges are awarded for case 2.

I am sure, though, that it counts the activity of the post at the time you edited it, not the current activity of the post after you edited it, say, 4 months ago.

Answer 2

I have coded up in SEDE what I believe to be a mostly accurate representation of the post edits that count toward Archaeologist. It identifies all your edits, and then discards those that were active within 6 months prior to the edit.

It uses the following strategy to determine the last activity of a post:

• activity on the question is what counts, whether the edit happened on an answer or the question itself
• question activity happens when the question is asked, edited, answered, or one of the answers is edited.

The vulnerability of this system is that SEDE does not record deleted content. Activity may be impacted by posts (answers) that are subsequently deleted. This is likely a relatively rare situation, and likely will not impact the counts too much.