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 priorwith 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 Revisionfrom  CandidateEdits as ceinner join FirstEdit as i on ce.EditId = i.EditIdinner join Revisions as r on ce.EditId = r.EditIdorder by EditDateThe 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
Archaeologist Progression
sql;sql server;stackexchange
A thing of beautyFor 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 nitpickI 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 ceinner join FirstEdit as i on ce.EditId = i.EditIdinner join Revisions as r on ce.EditId = r.EditId