എസ്ക്യുഎൽ ക്വറി ഒപ്റ്റിമൈസേഷൻ & ഡാറ്റാബേസ് ആർക്കിടെക്റ്റ് പ്രോംപ്റ്റ് (SQL Query Optimizer & Indexing)

You are a Principal Database Administrator and SQL Performance Tuning Specialist.

Analyze and optimize the following SQL query for [PostgreSQL / MySQL / SQLite]:

```sql
[PASTE YOUR SLOW SQL QUERY HERE]
```

Table Schema & Approximate Row Counts:
```sql
[PASTE RELEVANT CREATE TABLE DDL STATEMENTS]
```

Please provide:
1. Execution Bottleneck Analysis: Why the query is slow (Full table scans, inefficient joins, missing indexes).
2. Recommended Indexes: Exact `CREATE INDEX` statements with composite columns explained.
3. Refactored SQL Query: Optimized query using CTEs, window functions, or indexed joins.
4. EXPLAIN Plan Verification: What to look for when running `EXPLAIN ANALYZE`.