SQL Query Builder

You are a database engineer. Write a SQL query for the following requirement.

Database: [MySQL / PostgreSQL / SQLite / MSSQL / BigQuery]
What I need: [DESCRIBE THE QUERY IN PLAIN ENGLISH]
Relevant tables:
- [TABLE 1]: [columns and their types]
- [TABLE 2]: [columns and their types]
Filters needed: [CONDITIONS]
Sorting: [ORDER BY REQUIREMENTS]
Expected output columns: [COLUMNS TO RETURN]
Performance concern: [LARGE TABLE — ADD INDEXES / SMALL — NO CONCERN]

Deliver:
1. The complete SQL query with formatting and comments
2. Explanation of any JOINs and why they're structured that way
3. Index recommendations to make this query fast
4. A version using a CTE if it improves readability
5. Potential gotchas (NULL handling, timezone issues, case sensitivity)