Feature Adoption Rate — Solution & ExplanationProblems / Feature Adoption Rate / EditorialApproach

Filter to eligible adopters. Inner-join feature_usage to active_users on user_id. This drops usage from churned users (the data plants one such row) — adoption is defined over the active population only.

Distinct adopters per feature. COUNT(DISTINCT user_id) per feature. Plain COUNT(*) double-counts users who used a feature repeatedly — the log contains several duplicate user+feature rows precisely to punish that.

LEFT JOIN from the catalog. Start from features so the never-used feature survives with NULL adopter counts; COALESCE(..., 0) turns that into 0 adopters and 0.00 percent.

Scalar denominator. (SELECT COUNT(*) FROM active_users) is the same number for every row — a scalar subquery in SQL, or a 1-row aggregate crossJoined on in PySpark. With 8 active users each adopter is worth exactly 12.5 points, so the percentages are exact at 2 decimals.

Order by feature.

Key InsightAdoption rate questions are really population questions: the denominator comes from a table that is *not* the event log. Deriving the denominator from feature_usage (e.g. distinct users seen in the log) silently shrinks the population to people who used *something*, inflating every rate — and it can never produce the 0.00% row, because a feature with no usage has no rows to group. The features-spine LEFT JOIN plus an independent denominator table is the pattern that gets both right.Hints

Three roles, three tables: feature_usage supplies the numerator events, active_users defines both the eligible population and the denominator, and features is the spine every output row hangs off.

Adopters must be COUNT(DISTINCT user_id), not COUNT(*) — the log has repeat rows for the same user+feature. And inner-join usage to active_users first: one usage row belongs to a churned user who is not active and must not count.

If you start the final query FROM feature_usage you can never see the feature nobody used. Start FROM features and LEFT JOIN the per-feature adopter counts, coalescing NULL to 0.

The denominator is a single scalar (total active users). In SQL a scalar subquery works; in PySpark compute a 1-row DataFrame with .agg(F.count('*')) and .crossJoin it onto every feature row.

Solve Feature Adoption Rate yourself →