SQL: Weekly Activation Rates | Dataford Interview Questions

Write a SQL query to calculate weekly activation rates by acquisition channel and cohort for Spring Health. Use the members table and the weekly_calendar table. Treat a member as activated when activation_at falls within the reporting week, and label missing acquisition channels as Unknown.

Output

One row per acquisition channel, signup cohort week, and reporting week through 2024-01-29.
Columns: acquisition_channel, cohort_week, week_start, cohort_members, activated_members, and activation_rate.
Include zero-activation weeks and sort by acquisition channel, cohort week, then week start.

Schema

members

Column	Type	Description
member_idPK	INT	Unique Spring Health member identifier
signup_at	TIMESTAMP	Timestamp when the member signed up
acquisition_channel	VARCHAR(50)	Marketing channel attributed to the signup
activation_at	TIMESTAMP	Timestamp when the member completed activation

weekly_calendar

Column	Type	Description
week_startPK	DATE	Monday starting the reporting week
week_end	DATE	Sunday ending the reporting week

Your question is SQL: Weekly Activation Rates. 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