0

Please login or register to vote for this query.

(click on this box to dismiss)

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

DECLARE @LastDate datetime
SELECT @LastDate = MAX(CreationDate) FROM PostHistory
DECLARE @SiteAge int = DATEDIFF(DAY, '2017-02-28 19:00:00', @LastDate);

WITH Threshold AS (
  SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY Id) Days FROM PostHistory
),

PostsFixed AS (
  SELECT Id, PostTypeId, CreationDate, ClosedDate, DeletionDate
  FROM PostsWithDeleted 
  WHERE PostTypeId = 1
  UNION ALL
  -- probably don't need the coalesce and can use a.deletiondate but whatever.
  SELECT a.Id, a.PostTypeId, q.CreationDate, q.ClosedDate, COALESCE(a.DeletionDate, q.DeletionDate) DeletionDate
  FROM PostsWithDeleted q INNER JOIN PostsWithDeleted a ON a.ParentId = q.Id
)

-- Time threshold based on *question* creation date

SELECT
  Days,
  ##Area51RefValue?2.0## Area51Ref,
  1.0 * AllAnswers / AllQuestions [All],
  1.0 * NondeletedAnswers / NondeletedQuestions Nondeleted,
  1.0 * OpenNondeletedAnswers / OpenNondeletedQuestions OpenNondeleted
FROM (
  SELECT
   Days,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1) AllQuestions,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1 AND DeletionDate IS NULL) NondeletedQuestions,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1 AND DeletionDate IS NULL AND ClosedDate IS NULL) OpenNondeletedQuestions,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 2) AllAnswers,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 2 AND DeletionDate IS NULL) NondeletedAnswers,
   (SELECT COUNT(*) FROM PostsFixed p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 2 AND DeletionDate IS NULL AND ClosedDate IS NULL) OpenNondeletedAnswers
  FROM Threshold
  WHERE Days <= @SiteAge
) x

fork query

edited apr 9 17

created apr 9 17 Jason C