WITH PostsFixed AS ( SELECT Id, PostTypeId, ClosedDate, D...

0

Please login or register to vote for this query.

(click on this box to dismiss)

DevOps

Q&A for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

WITH PostsFixed AS (
  SELECT Id, PostTypeId, ClosedDate, DeletionDate
  FROM PostsWithDeleted 
  WHERE PostTypeId = 1
  UNION ALL
  SELECT a.Id, a.PostTypeId, q.ClosedDate, COALESCE(a.DeletionDate, q.DeletionDate) DeletionDate
  FROM PostsWithDeleted q INNER JOIN PostsWithDeleted a ON a.ParentId = q.Id
),

Counts AS (
  SELECT
    'All' What,
    SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
    SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
  FROM PostsFixed
  UNION ALL SELECT
    'All not deleted' What,
    SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
    SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
  FROM PostsFixed WHERE DeletionDate IS NULL
  UNION ALL SELECT
    'Open and not deleted' What,
    SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
    SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
  FROM PostsFixed WHERE DeletionDate IS NULL AND ClosedDate IS NULL
)

SELECT
  *,
  1.0 * Answers / Questions AnswersPerQuestion
FROM
  Counts

Enter Parameters

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