Add lock reasons to Data Explorer

Question

Posts are locked for different reasons. Some are in effect permanent ("historical significance", wiki-lock), some are meant to be temporary ("disputes are being resolved", "off-topic comments"). Sometimes, posts of the latter category remain in locked state long after any comments or debates are forgotten. Unless someone finds them and asks a moderator to unlock, that is.

One can search for locked posts both via SEDE and site search. But it appears impossible to search for a particular lock category. For example, one can't filter out historical locks which can't even be flagged.

I didn't find the reason for locking anywhere in SEDE (looked at all columns of the PostHistory table entries of locked posts.) I propose adding lock reasons to SEDE, either as a LockReasonTypeId or as the text of the lock notice.

Answer 1

You can since the introduction of PostNotices to the SEDE schema to get this information. You join posthistory records with a historytypeid of 14 (locked) with postnotices and postnoticetypes. The postnoticetypes except with class = 2 (bounty) will give you the notice that went with the lock and that signifies the type.

Here is the query

select  
       pnt.name
     , phl.creationdate
     , pn.postid as [Post Link]
from postnotices pn
inner join postnoticetypes pnt on pnt.id = pn.postnoticetypeid
inner join posthistory phl on phl.postid = pn.postid
-- exclude posts that are no longer locked
left outer join posthistory phu on phu.postid = pn.postid 
                          and phu.posthistorytypeid = 15 -- unlock
                          and phu.id > phl.id -- later
where pnt.classid <> 2 -- bounty
and phl.posthistorytypeid = 14 -- locked
and phu.id is null

When run today this is what the result looks like:

Keep in mind SEDE is only updated once a week, on Sunday morning. Hop in chat to say hi and give the awesome tutorial a try.