So, I'm continuing my dig into the protection feature on my site for a data deep dive and I'm getting stuck trying to find this data.
I'm looking this time for the list of all questions that have been protected at any point on the site (whether or not they currently are) along with the number of deleted answers on those questions.
I'd also like it to output the number of deleted answers on the question from users currently with 1 reputation, if possible.
I have made an attempt at getting this to work but I'm stuck before even getting the deleted answer counts:
SELECT ph.PostId, p.Id AS [Post Link], ph.CreationDate AS [protected At], COUNT(DISTINCT(p.Id)) AS 'Deleted Answers' FROM PostHistory ph INNER JOIN PostsWithDeleted p ON ph.PostId = p.Id WHERE ph.PostHistoryTypeId = 19 AND p.PostTypeId = 2 -- Answer AND p.DeletionDate IS NOT NULL Group by ph.PostId ORDER BY [protected At] DESC;