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.
Output
- One row per department with
department_name, school_name, active_users, completed_requests, average_resolution_hours, monthly_target, and completion_rate
- Include departments with no matching activity or target data, using zero for count metrics and NULL where an average or rate cannot be calculated
- Order by
school_name, then department_name
Output details
- Count distinct users with
user_status equal to Active for active_users.
- Count only activities with
activity_status equal to Completed for completed_requests and average_resolution_hours.
- Round
average_resolution_hours and completion_rate to two decimal places.
- Treat January 2025 as inclusive of
2025-01-01 and exclusive of 2025-02-01.
- Return
NULL for completion_rate when monthly_target is NULL or zero, while preserving monthly_target as NULL.
- Preserve every matching target record, allowing duplicate department rows when multiple target records exist.
