A community volunteering platform records every activity completed by its volunteers. The operations team wants to measure how many weekly participants fail to return the following week.
Table: volunteer_activity
+---------------+--------------+
| Column Name | Type |
+---------------+--------------+
| volunteer_id | integer |
| activity_id | integer |
| activity_type | varchar(255) |
| activity_date | date |
+---------------+--------------+
+---------------+--------------+
| Column Name | Type |
+---------------+--------------+
| volunteer_id | integer |
| activity_id | integer |
| activity_type | varchar(255) |
| activity_date | date |
+---------------+--------------+
The combination of volunteer_id and activity_id is the primary key. Each row records one volunteer activity.
Consider only activities dated from 2024-07-01 through 2024-07-31, inclusive. Group activity into Monday-starting weeks. A volunteer is considered active in a week if they completed at least one activity during that week; multiple activities by the same volunteer in the same week count only once.
For each eligible week, calculate the drop-off rate:
volunteers active in the current week but not active in the following week / volunteers active in the current week
Only return a week when the filtered July activity contains at least one active volunteer in the immediately following Monday-starting week. This means a final week without a represented following week is excluded.
Return week, the Monday date for the week, and dropoff_rate, rounded to 2 decimal places. Order the results by week ascending.
During the week of July 1, volunteers 1, 2, and 3 are active. Volunteers 1 and 3 return the next week, so 1 of 3 volunteers drops off: 0.33.
During the week of July 8, volunteers 1, 3, and 4 are active. Only volunteer 4 returns during the week of July 15, so 2 of 3 volunteers drop off: 0.67. The week of July 15 is excluded because the filtered data contains no activity in the immediately following week.
