Activation Rate by Signup Week — Solution & ExplanationProblems / Activation Rate by Signup Week / EditorialApproach

Qualify activations. Join signups to project_created events on user and the per-user window [signup_time, signup_time + 7 days). SELECT DISTINCT user_id collapses multiple qualifying events to one activation.

Rejoin to the full population. LEFT JOIN the activated set back onto *all* signups. This preserves the denominator — inner-joining events directly into the weekly aggregate would drop never-activated users and inflate the rate.

Weekly rollup. Group by DATE_TRUNC('week', signup_time); COUNT(*) vs COUNT(a.user_id) gives signups vs activated in one pass, then the ratio rounds to 2 decimals.

Key InsightActivation metrics have a two-population shape: a numerator set defined by behavior (did the key action in the window) and a denominator set defined by existence (signed up that week). Compute the numerator set separately, *then* attach it to the denominator — the LEFT JOIN + COUNT(column) idiom. Collapsing both into one join tempts two classic errors: losing non-activators (inner join) and double-counting eager users (no DISTINCT).Hints

The 7-day activation window is anchored to each user's own signup_time — it must be part of the signups-to-events join, not a constant date filter.

Dedupe the activated users (DISTINCT) before joining back: a user with two qualifying project_created events would otherwise be counted as two activated signups.

After LEFT JOINing the activated set onto signups, COUNT(*) counts everyone and COUNT(a.user_id) counts only matches — the NULL-skipping of COUNT(column) is the whole trick.

DATE_TRUNC('week', ...) is Monday-start in DuckDB, which is exactly what the problem asks for — no manual weekday arithmetic needed.

Solve Activation Rate by Signup Week yourself →