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