Archaeologist Progression

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 EditGUID
group by EditId
having count(distinct RevisionGUID) = 0

), FirstEdit as (

-- Only one

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

StackExchange Code Review Q#72139, answer score: 4