Your question is SQL Joins and CTEs. Start with the requirements and the four 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.
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 |