MongoDB Aggregation Pipeline Builder

Build a MongoDB aggregation pipeline for this requirement:

**Collection Schema:**
```json
{{COLLECTION_SCHEMA}}
```

**Sample Document:**
```json
{{SAMPLE_DOCUMENT}}
```

**Aggregation Requirement:** {{REQUIREMENT}}
**Expected Output Format:** {{OUTPUT_FORMAT}}

Please provide:
1. Complete aggregation pipeline with stages:
   - $match, $group, $project, $lookup, etc.
2. Explanation of each stage
3. Index recommendations for pipeline optimization
4. Performance considerations
5. Alternative pipeline approaches
6. Handling edge cases
7. Testing with sample data
8. Comparison with SQL equivalent