0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @query_date DATE = GETDATE() - 90;

WITH ImpactedQuestions AS (SELECT
                             YEAR(DATEADD(DAY, -365, p.CreationDate))  AS JudgmentYear,
                             MONTH(DATEADD(DAY, -365, p.CreationDate)) AS JudgmentMonth,
                             COUNT(*)                                  AS Total
                           FROM PostsWithDeleted p
                           WHERE p.PostTypeId = 1
                                 AND p.Score = CASE WHEN p.OwnerUserId IS NULL
                             THEN 1
                                               ELSE 0 END
                                 AND p.CreationDate < DATEADD(DAY, -365, @query_date)
                                 AND p.AnswerCount = 0
                                 AND p.ViewCount <= DATEDIFF(DAY, p.CreationDate, @query_date) * 1.5
                                 AND p.DeletionDate IS NULL
                                 AND COALESCE((SELECT TOP 1 ph.PostHistoryTypeId
                                               FROM PostHistory ph
                                                 INNER JOIN PostHistoryTypes pht ON ph.PostHistoryTypeId = pht.Id
                                               WHERE ph.PostId = p.Id
                                                     AND pht.Name IN ('Post Locked', 'Post Unlocked')
                                               ORDER BY ph.Id DESC), 0) <> 14
                           GROUP BY YEAR(DATEADD(DAY, -365, p.CreationDate)),
                             MONTH(DATEADD(DAY, -365, p.CreationDate))
                             , p.CreationDate, p.Id)


SELECT
  iq.JudgmentYear,
  iq.JudgmentMonth,
  SUM(iq.Total)
FROM ImpactedQuestions iq
GROUP BY iq.JudgmentYear, iq.judgmentMonth
ORDER BY iq.JudgmentYear ASC, iq.judgmentMonth ASC

view current version

edited aug 31 16

created aug 31 16 Makoto