Write a SQL query to calculate weekly volunteer engagement metrics by site, including total shifts, completed shifts, and completion rate.
Use the habitat_sites and volunteer_shifts tables. Include only shifts with a non-null date that reference a valid site. Treat shifts with a completed status as completed.
site_id, site_name, week_start, total_shifts, completed_shifts, and completion_rate.completion_rate should be a percentage rounded to two decimal places. Sort by week_start, then site_id.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique identifier for a Habitat for Humanity build or volunteer site |
| site_name | VARCHAR(150) | Display name of the volunteer site |
| city | VARCHAR(100) | City where the site is located |
| Column | Type | Description |
|---|---|---|
| shift_idPK | INT | Unique identifier for a volunteer shift |
| site_id | INT | Site assigned to the shift |
| shift_start | TIMESTAMP | Scheduled start timestamp for the shift |
| status | VARCHAR(30) | Shift status, such as completed, scheduled, cancelled, or no_show |