Days to Close a Duplicate Question by Creation Month

8

Please login or register to vote for this query.

(click on this box to dismiss)

Internet of Things Meta

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

SELECT date1 as creationDate, AVG(diff1) as avgDaysToClose
FROM (

  SELECT 
    DATEDIFF(day, d.CreationDate, d.ClosedDate) as [diff1],
    FORMAT (d.CreationDate, 'yy-MM') as [date1],
    FORMAT (d.ClosedDate, 'yy-MM') as [date2],
    (d.Id) as id
  FROM Posts d 
    LEFT JOIN PostHistory ph ON ph.PostId = d.Id
    LEFT JOIN PostLinks pl ON pl.PostId = d.Id
    LEFT JOIN Posts o ON o.Id = pl.RelatedPostId  -- o=original
  WHERE
    d.PostTypeId = 1  -- 1=Question
    AND pl.LinkTypeId = 3  -- 3=duplicate
    AND ph.PostHistoryTypeId = 10  -- 10=Post Closed
    
  ) as t1
group by date1
order by date1

Enter Parameters

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