Write the SQL, then explain what it will cost

Write the query for this question: {{describe what you need to know}}.

Schema: {{paste DDL or table and column list with row counts}}
Database: {{engine and version}}

Requirements:
1. Give the query, formatted readably, with comments only where the logic isn't obvious.
2. Explain in plain language what it does step by step.
3. State the expected plan: which indexes it will use, what will be scanned, and the rough cost at the row counts I gave.
4. Name the indexes I'm missing, with the exact CREATE INDEX statement and what else they'd help or hurt.
5. Show a second version optimised differently (e.g. window function versus join, CTE versus subquery) and say when each wins.
6. Flag correctness traps in my question: NULL handling, duplicate rows from joins, timezone assumptions, and whether "per day" means UTC or local.
7. Tell me how to verify the result is right with a cheap sanity check.

Never invent columns. If the schema can't answer the question, say what's missing.