Archaeologist Progression

Question

One of the badges available on Stack Exchange is the Archaeologist badge. This is awarded for making 100 edits on posts that had been inactive (at the time of the edit) for 6 months.

There is no indication on your profile, or elsewhere, how close you are to this badge. Here's an SEDE query that can give you a good idea of how close you are to being awarded Archaeologist:

-- Title or text edits on someone else's post
-- that had been idle for 6 or more months prior

with CandidateEdits as (

    -- Edits the user has made to titles/body of some other user's posts.

    select min(ph.Id) as EditId,
           ph.PostId EditPost,
           IsNull(p.ParentId, p.Id) as EditQuestion,
           ph.RevisionGUID as EditGUID,
           ph.CreationDate as EditDate
    from PostHistory as ph
        inner join Posts as p on ph.PostId = p.Id and OwnerUserId != UserId
    where UserId = ##UserId:int##
      and PostHistoryTypeId in (4,5) --Title/Body edits.
      and PostTypeId in (1, 2) -- question/answer
    group by ph.PostId, p.Id, p.ParentId, ph.RevisionGUID, ph.CreationDate

), RelatedPosts as (

    -- Posts that cannot be modified in the 6-month window

    select EditId,
           EditDate,
           EditGUID,
           EditQuestion as RelatedPost
    from CandidateEdits

    UNION

    select EditId,
           EditDate,
           EditGUID,
           Id as RelatedPost
    from CandidateEdits
    inner join Posts on EditQuestion = ParentId
         and PostTypeId = 2

), Inactives as (

    -- Edits that have no activity related posts in the 6 month window.
    -- Use Left join, and have a count of 0

    select EditId,
           count(distinct RevisionGUID) as Invalidations
    from RelatedPosts Left Outer Join PostHistory on
             RelatedPost = PostId
         and CreationDate between DateAdd(mm, -6, EditDate) and EditDate
         and Id < EditId
         and RevisionGUID <> EditGUID
    group by EditId
    having count(distinct RevisionGUID) = 0

), FirstEdit as (

    -- Only one of multiple edits on the same post count toward the badge.

    select ce.EditPost,
           min(i.EditId) as EditId
    from CandidateEdits as ce
      inner join Inactives as i on ce.EditId = i.EditId
    group by ce.EditPost

), Revisions as (

    -- Identify which revision in the system relates to the edit you made.
    -- convert to NVarchar to make concatenation simpler in result.

    select EditID,
           Convert(NVarchar(10), EditPost) as RevPost,
           Convert(NVarchar(10), count(distinct RevisionGUID)) as Revision
    from CandidateEdits inner join PostHistory
      on  PostId = EditPost
      and Id <= EditId
      and PostHistoryTypeId < 10
    group by EditId, EditPost
)

select Rank() over (order by EditDate) as Dig,
       ce.EditPost as [Post Link],
       EditDate,
       'site://revisions/' + RevPost + '/' + Revision
             + '|Revision ' + Revision as Revision
from  CandidateEdits as ce
inner join FirstEdit as i on ce.EditId = i.EditId
inner join Revisions as r on ce.EditId = r.EditId
order by EditDate

The logic for the inactive posts is complicated: A new, or edited answer, or an edit to the question itself, resets the 'inactive' counter. The most recent change to any of the answers, or the question itself, is what is used regardless of whether you are editing the question, or one of its answers.

Edits to the tags do not count, only to the title, or body of the post.

Because you may make multiple edits to the post, we only want to display the one edit that counts, and the revision it belongs to.

All suggestions welcome

Answer 1

A thing of beauty

For such an odd/complicated logic, you certainly have made your code easy to follow along. Your comments are succinct and well used to label the CTEs.

Your CTE names are perfect for the code to read fluidly. The spacing and indentation is both useful and consistent. You certainly know how to write some SQL.

---

Small nitpick

I feel that the table aliases are not the best, and perhaps not even needed, since the table names on SEDE are not particularly long and they are quite descriptive. i especially gave me pause.

from  CandidateEdits as ce
inner join FirstEdit as i on ce.EditId = i.EditId
inner join Revisions as r on ce.EditId = r.EditId

Answer 2

select EditId,
       count(distinct RevisionGUID) as Invalidations
from RelatedPosts Left Outer Join PostHistory on
         RelatedPost = PostId
     and CreationDate between DateAdd(mm, -6, EditDate) and EditDate
     and Id < EditId
     and RevisionGUID <> EditGUID
group by EditId
having count(distinct RevisionGUID) = 0

you should change the between statement to a greater than or equal to, less than statement.

CreationDate > DateAdd(mm,-6, EditDate) AND CreationDate <= EditDate

but I assume that this was well thought out and that you didn't want to include the day the post was created, and didn't want to include the day that the post was edited. I haven't taken the time to read into the finer details of how this particular badge works so I don't know if this would make a difference when it came down the the minute or the hour.

Also, some people would claim that a "not equal" in the where clause is slow and that you should try to recreate something like this with a join.

Neither table in this query is aliased so (because I don't look at this database enough) I cannot tell which column is coming from which table and how it would look in a join or if this is simply the easiest way to go.

So, I would say Alias your tables, which is what you are doing for a majority of the query.

---

from PostHistory as ph
    inner join Posts as p on ph.PostId = p.Id and OwnerUserId != UserId

In this piece of code you use != but in the code I pointed to above you use <>. I am sure it works with the Engine that SEDE uses or it is parsed out to the correct operator before submitted to the actual SQL Engine. just thought I would point out the inconsistency because I am sure that some SQL Engines won't like one or the other.