Your question is Optimizing Complex SQL Joins. Start with the requirements and the five tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
How would you optimize a slow-running SQL query that involves multiple heavy joins and aggregations on partitioned tables?
Use the supplied project, event, cost, and assignment tables to produce a representative optimized report for active projects overlapping January and February 2025. Preserve active projects that have no matching events or costs.
client_name, project_id, billable_hours, revenue, total_cost, and consultant_count.revenue descending, then project_id ascending.| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Unique client identifier |
| client_name | VARCHAR(100) | Client display name |
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Unique project identifier |
| client_id | INT | Owning client |
| project_name | VARCHAR(120) | Project name |
| status | VARCHAR(20) | Project lifecycle status |
| start_date | DATE | Project start date |
| end_date | DATE | Project end date, if completed |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique project event identifier |
| project_id | INT | Related project |
| event_date | DATE | Event date and partitioning key |
| event_type | VARCHAR(40) | Type of project activity |
| hours | NUMERIC(10,2) | Hours recorded for the event |
| billable | BOOLEAN | Whether the hours are billable |
| billing_rate | NUMERIC(10,2) | Hourly billing rate |
| Column | Type | Description |
|---|---|---|
| cost_idPK | INT | Unique cost identifier |
| project_id | INT | Related project |
| cost_date | DATE | Cost date and partitioning key |
| category | VARCHAR(50) | Cost category |
| amount | NUMERIC(12,2) | Cost amount |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment identifier |
| project_id | INT | Related project |
| consultant_id | INT | Consultant identifier |
| consultant_name | VARCHAR(100) | Consultant name |