Audit the dates and timezones in this query

Numbers are slightly off and the period boundary is the usual suspect.

Check every date and time expression in this query. Quote each fragment, then tell me which timezone the value is in, whether BETWEEN is dropping the final day, what happens to rows landing exactly on a boundary, and whether the timestamp driving the period is the one I think it is.

Query:

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