SQL Query Construction & Optimization

Write an optimized SQL query for this task.

What I need to do: [Describe what data you need to retrieve or manipulate]

Database schema:

[Provide table structures - table names, column names, data types, relationships]

Specific requirements:

• Filters: [what conditions to apply]
• Joins: [which tables need to be connected and how]
• Aggregations: [sums, counts, averages needed]
• Sorting: [how results should be ordered]
• Limit: [how many rows to return]

Performance context:

• Table sizes: [approximate row counts]
• Indexes available: [which columns are indexed]
• Performance target: [acceptable query time]

What I need from you:

1. The complete SQL query (formatted and readable)
2. Explanation of complex parts (joins, subqueries, window functions)
3. Why you structured it this way (optimization reasoning)
4. Potential performance issues (if tables grow, etc.)
5. Alternative approaches if applicable

SQL dialect: [MySQL/PostgreSQL/SQL Server/Oracle - specify]

Success criteria: Query runs correctly, efficiently, and I understand how it works.