Write the query from a plain-language question

You know exactly what you want to know and not how to express it in SQL.

Write a SQL query answering the question below against the schema I give.

Before writing:
1. Restate the question as a precise specification, including the grain of the output, the time window, and how ties or duplicates should be handled.
2. List every ambiguity in my wording and the interpretation you chose. If an ambiguity would materially change the answer, ask instead of choosing.

Then write the query. Requirements:
- Use CTEs rather than nested subqueries, named for what they contain.
- Only reference columns present in the schema I gave. Never invent one.
- Handle NULLs explicitly wherever they could appear in a join key, a filter, or an aggregate.

After the query, give one sentence on what a plausible result looks like, so I can tell if it ran wrong.

Question:
Schema (tables, columns, types):
Dialect:

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