How do you handle a request from a stakeholder who wants everything on one dashboard at The Johns Hopkins University?
For this exercise, write a SQL query that produces one dashboard-ready row for every department, including departments without activity records. Restrict activity metrics to January 2025.
department_name, school_name, active_users, completed_requests, average_resolution_hours, monthly_target, and completion_rateschool_name, then department_name| Column | Type | Description |
|---|---|---|
| department_idPK | INT | Unique department identifier |
| department_name | VARCHAR(100) | Department name |
| school_name | VARCHAR(100) | School or division containing the department |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique dashboard user identifier |
| department_id | INT | Department associated with the user |
| user_status | VARCHAR(20) | Current user status |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity identifier |
| user_id | INT | User associated with the activity |
| activity_date | DATE | Date of the activity |
| activity_status | VARCHAR(20) | Status of the activity |
| resolution_hours | DECIMAL(8,2) | Hours required to resolve a completed activity |
| Column | Type | Description |
|---|---|---|
| target_idPK | INT | Unique target record identifier |
| department_id | INT | Department associated with the target |
| monthly_target | INT | Target number of completed activities for the month |