0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- PostHistoryType 10: Post Closed

WITH FirstPostClosure AS (
  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
  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 CONVERT(datetime, percentile_cont(0.5)
  within GROUP (ORDER BY CONVERT(float, PostClosed - PostCreated)) OVER())
  AS 'Median close time for questions'
FROM FirstPostClosure

fork query

edited aug 22 18 Vogel612

created jun 30 18 ccorn