Counting the number of deleted answers on questions that have been protected

Question

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;

Answer 1

Here you go: https://data.stackexchange.com/rpg/query/1262647

GROUP BY tends to be confusing and I don't have a trick for getting it right; I just 'know' which fields I need to group on. Or the compiler tells me I'm wrong ... It feels strange to have to group on two fields from the same table, but that's just how SQL works.

The join had a mistake too; I've changed it to ph.PostId = p.ParentId because the PostId of the PostHistory table is the ID of the question, and the Id of the PostsWithDeleted table is the ID of the answer. Filtering on PostTypeId isn't necessary because only answers have their ParentId filled.

I'd also like it to output the number of deleted answers on the question from users currently with 1 reputation, if possible.

That's not going to work; user information on deleted answers is not present in SEDE.