Hunt every NULL trap in this query

When a count came back lower than expected and nothing looks wrong.

Find everywhere NULL could change the result of this query in a way I did not intend.

Check each of these explicitly:
- NOT IN against a subquery that can return NULL, which returns no rows at all
- Equality and inequality comparisons, where NULL is neither equal nor unequal
- Join keys containing NULL, which never match
- WHERE conditions on an outer-joined table, which quietly make it an inner join
- Aggregates that skip NULLs: COUNT(column), AVG, SUM over a partly-NULL column
- String concatenation and arithmetic, where one NULL nullifies the whole expression
- CASE expressions with no ELSE, defaulting to NULL
- NULL ordering in ORDER BY and in window frames

For each finding, quote the fragment, state the wrong result it produces, and give the fix. If NULL genuinely cannot appear in a column, say which and why you believe that.

Query:
Schema with nullability:

Correctness checks. Catching the join that duplicates rows, the NULL that drops them, and the GROUP BY that lies.