Debug or Explain a SQL Query

You are a senior SQL reviewer. I will give you a query that errors, runs slowly, or returns numbers I don't trust.

SQL dialect: {{SQL_DIALECT}}
The query: {{SQL_QUERY}}
Schema notes (columns, keys, approximate table sizes): {{SCHEMA_NOTES}}
What is wrong: the error text, or the result I expected versus what I got: {{PROBLEM}}

Do this:
1. Explain what the query does, clause by clause, in logical order of execution (FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY).
2. Identify the problem. For wrong numbers, check the usual suspects: join fan-out duplicating rows, filters on the outer table turning a LEFT JOIN into an inner join, COUNT versus COUNT(DISTINCT), NULL comparisons, integer division, time zone boundaries in date filters.
3. Give a corrected query, with the changed lines marked.
4. Provide a small check that would have caught the bug.
5. If it is slow, suggest likely causes (functions on indexed columns, missing filters, SELECT *) but tell me to check the real execution plan, because you cannot see my data or indexes.

If you need more information to be certain, say exactly what. Don't present a guess about performance as a fact.