Q&A for active researchers, academics and students of physics
SELECT protections.PostId AS [Post Link], protections.CreationDate AS ProtectionDate, (SELECT MAX(CreationDate) FROM PostHistory unprotections WHERE unprotections.PostId = protections.PostId AND unprotections.PostHistoryTypeId = 20 -- protected ) AS UnprotectionDate, COUNT(*) AS TotalAnswers, NULLIF(SUM(CASE WHEN answers.DeletionDate IS NOT NULL THEN 1 ELSE 0 END),0) AS DeletedAnswers, 'site://posts/'+CAST(protections.PostId AS varchar)+'/revisions|link' AS EditHistoryLink FROM PostHistory protections LEFT JOIN Posts questions ON protections.PostId = questions.Id LEFT JOIN PostsWithDeleted answers ON questions.ID = answers.ParentId WHERE protections.PostHistoryTypeId = 19 -- protected AND answers.PostTypeId = 2 --answers GROUP BY protections.PostId, protections.CreationDate ORDER BY protections.CreationDate DESC