Check my aggregation for grouping traps

Any query with GROUP BY that feeds a number someone will act on.

Review the aggregation in this query specifically for grouping mistakes.

Check:
- Does every non-aggregated column in the SELECT appear in the GROUP BY, and is each one functionally dependent on the group key? A column that varies within a group will silently pick an arbitrary value in some dialects.
- Are groups with zero rows missing from the output entirely? If I need zeroes, I need a calendar or dimension table joined in.
- Is HAVING doing work that belongs in WHERE, or the reverse?
- Is COUNT(*) counting rows when I meant distinct entities?
- Am I averaging a ratio that should be a ratio of sums?
- Does a filter on an aggregate remove groups I intended to keep at zero?

For each problem, show the corrected fragment. Where the aggregation is right, say so and move on.

Query:
What each output row should represent:

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