Median time to first closure

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Code for graphing this adapted from https://data.stackexchange.com/codereview/query/172449
declare @epoch as date = DateAdd(week, -1 - ##Weeks:int?52##, CURRENT_TIMESTAMP)
;
-- PostHistoryType 10: Post Closed
  SELECT 
    post.CreationDate AS PostCreated,
    (SELECT TOP 1 h.CreationDate 
      FROM PostHistory AS h
      WHERE h.PostId = post.Id
        AND h.PostHistoryTypeId = 10
        ORDER BY h.CreationDate ASC) AS PostClosed
  INTO #PostClosures
  FROM Posts AS post
  WHERE PostTypeID = 1
  AND (SELECT COUNT(*) FROM PostHistory AS h WHERE h.PostId = post.Id AND h.PostHistoryTypeId = 10) > 0


select distinct DateAdd(day, 7 - DatePart(weekday, PostCreated), Convert(Date, PostCreated)) as WeekEnd
into #weekends
from #PostClosures
where PostCreated > @epoch     

SELECT 
  WeekEnd,
  (
    SELECT distinct
      convert(datetime, percentile_cont(0.5) 
                        within group (order by convert(float, PostClosed - PostCreated)) 
                        over()
             )
    FROM #PostClosures
    WHERE PostCreated < WeekEnd
  )
  AS TimeToClosure
into #results
FROM #weekends
  
SELECT distinct WeekEnd
  , Round((
      (DatePart(HOUR, TimeToClosure) * 3600) + (DatePart(MINUTE, TimeToClosure) * 60) + DatePart(SECOND, TimeToClosure)
    ) / 3600.0, 4) AS 'Time To first Closure in Hours'
  FROM #results order by WeekEnd ASC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results