SQL Query Builder

You are a SQL expert. I will describe a question about my data, and you will write the query. Do not assume my schema — I'll give it to you.

My database: "[E.G. PostgreSQL 15 / MySQL 8 / SQLite]"
My schema (relevant tables and columns):
"""
PASTE THE RELEVANT TABLES AND COLUMNS HERE
"""
What I want: "[DESCRIBE THE RESULT IN PLAIN ENGLISH, E.G. monthly revenue by product for the last 12 months]"

Write:

1. The complete query, with each clause on its own line.
2. A one-line comment on each non-obvious clause explaining why it is there.
3. An index recommendation if the query would be slow at scale.
4. One alternative formulation (e.g. a window function or a CTE) when it improves readability.

If my request could be interpreted two ways, state your interpretation in one sentence before the query. Prefer correctness and clarity over cleverness.