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 |