Feature Adoption, Including Features Nobody Used
The product team reviews adoption of every shipped feature, and a feature nobody has used yet must still appear with 0 — those are the ones to fix or retire.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #405
Problem statement
The product team reviews adoption of every shipped feature, and a feature nobody has used yet must still appear with 0 — those are the ones to fix or retire.
Return feature_name and adopters, the number of distinct users who used the feature at least once, for every row of features. Order by adopters descending, then feature_name ascending.
Tables
Table: features
| Column | Type |
|---|---|
| feature_id | int |
| feature_name | varchar |
| launched_on | date |
Primary key: feature_id.
One row per feature that has shipped.
Table: feature_usage
| Column | Type |
|---|---|
| usage_id | int |
| feature_id | int |
| user_id | int |
| used_at | datetime |
Primary key: usage_id.
One row each time a user used a feature; feature_id always names a row of features.
Examples
Example 1
features
| feature_id | feature_name | launched_on |
|---|---|---|
| 1 | Kanban Board | 2024-01-15 |
| 2 | Time Tracking | 2024-03-01 |
| 3 | AI Summaries | 2024-06-20 |
| 4 | Audit Log | 2024-07-01 |
feature_usage
| usage_id | feature_id | user_id | used_at |
|---|---|---|---|
| 1 | 1 | 21 | 2024-07-02 10:00:00 |
| 2 | 1 | 22 | 2024-07-02 11:30:00 |
| 3 | 1 | 21 | 2024-07-03 09:00:00 |
| 4 | 2 | 23 | 2024-07-03 12:15:00 |
| 5 | 3 | 21 | 2024-07-04 16:40:00 |
| 6 | 3 | 24 | 2024-07-05 08:20:00 |
| 7 | 2 | 23 | 2024-07-05 18:00:00 |
Output
| feature_name | adopters |
|---|---|
| AI Summaries | 2 |
| Kanban Board | 2 |
| Time Tracking | 1 |
| Audit Log | 0 |
How to solve Feature Adoption, Including Features Nobody Used
Start from features and LEFT JOIN feature_usage, so a feature without any usage survives as one row whose usage columns are NULL. Group by the feature and count DISTINCT u.user_id. Because COUNT(column) skips NULLs, the unused feature counts 0 rather than 1 — COUNT(*) would count its single NULL-padded row and report one adopter that does not exist.
DISTINCT turns usage rows into people: user 21 using the Kanban board twice is one adopter. Grouping by feature_id as well as the name keeps two features that happened to share a name apart.
A correlated subquery per feature gives the same count, and aggregating the usage table first then LEFT JOINing it needs COALESCE(…, 0) because the missing features get NULL from the join. Pre-aggregating is often the fastest plan on a big log: one pass over usage, then a small join.
Reference solution (MySQL)
SELECT f.feature_name, COUNT(DISTINCT u.user_id) AS adopters
FROM features f
LEFT JOIN feature_usage u ON u.feature_id = f.feature_id
GROUP BY f.feature_id, f.feature_name
ORDER BY adopters DESC, f.feature_nameAnother way
SELECT f.feature_name, (SELECT COUNT(DISTINCT u.user_id) FROM feature_usage u WHERE u.feature_id = f.feature_id) AS adopters FROM features f ORDER BY adopters DESC, f.feature_nameAnother way
SELECT f.feature_name, COALESCE(a.n, 0) AS adopters FROM features f LEFT JOIN (SELECT feature_id, COUNT(DISTINCT user_id) AS n FROM feature_usage GROUP BY feature_id) a ON a.feature_id = f.feature_id ORDER BY adopters DESC, f.feature_name← Free Trials Ending Within a Week · Days From Signup to First Captured Payment →