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 DISTINCT p.Id
                           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)
  , DuplicateCount AS (
    SELECT COUNT(*) AS count
    FROM ImpactedQuestions iq
      LEFT JOIN PostHistory ph ON ph.PostId = iq.Id
    WHERE ph.PostHistoryTypeId = 10 AND ph.Comment IN (1, 101)),
    ClosedCount AS (
      SELECT COUNT(*) AS count
      FROM ImpactedQuestions iq
        LEFT JOIN PostHistory ph ON ph.PostId = iq.Id
      WHERE ph.PostHistoryTypeId = 10)
SELECT
  dc.count                                                AS DuplicateCount,
  cc.count                                                AS AllClosedCount,
  (cc.count - dc.count)                                   AS Delta,
  1 - ((cc.count - dc.count) / (CAST(cc.count AS FLOAT))) AS Percentage
FROM DuplicateCount dc, ClosedCount cc

fork query

edited aug 31 16

created aug 31 16 Makoto