This is arguably the most complex query here because the double nesting.

The innermost query here is necessary because we want one row for each "action". Phabricator creates multiple rows when you eg. add a comment AND request changes, but we need to be able to see those combined so we can differentiate from somebody resigning with a review, versus just resigning (without reviewing)

JSON_ARRAYAGG lets us create an array with a list of the actions that were taken

In the second inner query, we try to filter to only those actions that constitute a proper review.

The first condition excludes "simple resignations", which are when somebody resigns without any inline comments.

The second condition looks to see if a review was made. Phabricator's transactions table includes entries for eg. referencing this diff in another diff, so we have to exclude those.

The three actions we're looking for in the second condition are:

1. Any inline comment
2. Any non-inline comment
3. Any status change (such as accepting or rejecting a diff)

I should also check to see if eg. accepting a diff when you're not blocking yields a "differential.revision.status". If not, I'll need to add some more ORs here for accepting or rejecting

This is arguably the most complex query here because the double nesting.

The innermost query here is necessary because we want one row for each "action". Phabricator creates multiple rows when you eg. add a comment AND request changes, but we need to be able to see those combined so we can differentiate from somebody resigning with a review, versus just resigning (without reviewing)

JSON_ARRAYAGG lets us create an array with a list of the actions that were taken

In the second inner query, we try to filter to only those actions that constitute a proper review.

The first condition excludes "simple resignations", which are when somebody resigns without any inline comments.

The second condition looks to see if a review was made. Phabricator's transactions table includes entries for eg. referencing this diff in another diff, so we have to exclude those.

The five actions we're looking for in the second condition are:

1. Any inline comment
2. Any non-inline comment
3. Accepting a diff
4. Rejecting a diff
5. Any status change (such as adding somebody as blocking)

Open to removing differential.revision.status, actually... it barely changes the numbers, and arguably a status change without an accompanying comment / accept / reject doesn't really count as a review