Protection Duration of Posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Quick and clunky examination of most recent protection duration of posts that were subsequently unprotected.

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

-- 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)'
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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results