Protection Duration of Posts

1

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.

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

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

Enter Parameters

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