Weekly Volunteer Engagement SQL | Dataford Interview Questions

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.

Output

One row per site and calendar week, with weeks starting on Monday.
Columns: 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.

Schema

habitat_sites

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

volunteer_shifts

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

Your question is Weekly Volunteer Engagement SQL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem