Explain how you use SQL joins and CTEs, and relate it to a past project you worked on.
Using the supplied tables, demonstrate the explanation with a query that summarizes active projects. Include projects without assignments or recorded hours so the result shows how unmatched records are handled.
project_id, project_name, assigned_consultants, total_hours, billable_hours, utilization_pct, and project_rank.billable_hours descending and project_id ascending, and assign equal ranks to projects with equal billable hours.| Column | Type | Description |
|---|---|---|
| consultant_idPK | INT | Consultant identifier |
| consultant_name | VARCHAR(100) | Consultant name |
| role | VARCHAR(80) | Consultant role |
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Project identifier |
| project_name | VARCHAR(150) | Project name |
| status | VARCHAR(20) | Project lifecycle status |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Assignment identifier |
| consultant_id | INT | Assigned consultant |
| project_id | INT | Assigned project |
| assignment_end | DATE | Assignment end date |
| Column | Type | Description |
|---|---|---|
| entry_idPK | INT | Time entry identifier |
| assignment_id | INT | Related assignment |
| entry_date | DATE | Date hours were recorded |
| hours | NUMERIC(10,2) | Recorded hours |
| billable | BOOLEAN | Whether the hours are billable |