SQL Query Optimization for Analysis

You are a SQL optimization expert. Optimize this query for better performance.

**Current Query:**
```sql
{slow_query}
```

**Problem:** {performance_issue}
**Database:** {database_type}
**Table Sizes:** {table_sizes}

Optimize the query:

**1. Analyze Current Query:**
```sql
EXPLAIN ANALYZE {slow_query}
```

Issues found:
- Sequential scans on large tables
- Missing indexes
- Inefficient joins
- Unnecessary subqueries

**2. Optimization Techniques:**

**A. Indexing:**
```sql
-- Add indexes on frequently filtered columns
CREATE INDEX idx_user_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id_date ON orders(user_id, order_date);
```

**B. Rewrite Subqueries:**
Before:
```sql
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
```

After:
```sql
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;
```

**C. Use CTEs for Readability:**
```sql
WITH recent_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.*, ro.order_count
FROM users u
LEFT JOIN recent_orders ro ON u.id = ro.user_id;
```

**D. Limit Early:**
Filter as early as possible, use WHERE before JOIN

**E. Aggregate Efficiently:**
Use window functions instead of self-joins
```sql
SELECT user_id,
       order_date,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) as running_total
FROM orders;
```

**3. Optimized Query:**
```sql
{optimized_query}
```

**4. Performance Comparison:**
| Metric | Before | After | Improvement |
|--------|--------|-------|-------------|
| Execution Time | {before_time} | {after_time} | {improvement}x |
| Rows Scanned | {before_rows} | {after_rows} | {reduction}% |

**5. Best Practices:**
- Always use WHERE before JOIN
- Index foreign keys
- Avoid SELECT *, specify columns
- Use LIMIT when testing
- Partition large tables by date
- Materialize complex aggregations

Provide: Optimized query + indexes + performance metrics.