Write this query, assumptions first

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

Write the query for the question below. Before the SQL, list every ambiguity in my wording and the reading you chose, and ask me rather than choosing where the answer would materially change. Use CTEs named for what they hold. Only reference columns present in the schema I gave.

Question:
Schema:
Dialect:

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