Port this query to another SQL dialect

Moving a query between Postgres, BigQuery, Snowflake, MySQL, or SQLite.

Convert the query below from the source dialect to the target dialect.

Rules:
- Produce a working query, not a sketch.
- After it, list every change you made and why, grouped: syntax, function names, date and interval handling, string functions, type casting, NULL and empty-string semantics, quoting and case sensitivity.
- Call out any behaviour that differs even though the syntax converts cleanly. Particularly: integer division, implicit casts, ordering of NULLs, string comparison collation, DISTINCT with NULLs, and how each dialect treats division by zero.
- Where the target has no equivalent, give the closest workaround and state what is lost.

Flag anything I must verify against real data rather than trust the conversion for.

Source dialect:
Target dialect:
Query:

Writing and reading queries. Drafting SQL from a plain question, making an inherited query legible, and moving it between dialects.