Quick and clunky examination of most recent protection duration of posts that were subsequently unprotected.
Q&A for community managers, administrators, and moderators
-- Most Recent Protections ; WITH RecentProtects (PostId, ProtectDate, Rank) AS ( select * from ( select PostId, CreationDate, ROW_NUMBER() over (partition by PostId order by CreationDate desc) as Rank from PostHistory where PostHistoryTypeId = 19)t where Rank = 1 ), -- Most Recent Unprotects RecentUnprotects (PostId, UnprotectDate, Rank) AS ( select * from ( select PostId, CreationDate, ROW_NUMBER() over (partition by PostId order by CreationDate desc) as Rank from PostHistory where PostHistoryTypeId = 20)t where Rank = 1 ) -- Show me the Duration of protection for the most recent unprotects SELECT un.PostId As [Post Link], Posts.CreationDate, ProtectDate, UnprotectDate, DATEDIFF(day, ProtectDate, UnprotectDate) as 'Protect Duration (days)', ViewCount, Score FROM RecentUnprotects AS un INNER JOIN RecentProtects AS pro ON un.PostId = pro.PostId INNER JOIN Posts on un.PostId = Posts.id WHERE UnprotectDate > ProtectDate