1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jan 3 18

created jan 3 18 E.P.