Feature Adoption, Including Features Nobody Used

Easy
405.

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.

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

ColumnType
feature_idint
feature_namevarchar
launched_ondate

Primary key: feature_id.

One row per feature that has shipped.

Table: feature_usage

ColumnType
usage_idint
feature_idint
user_idint
used_atdatetime

Primary key: usage_id.

One row each time a user used a feature; feature_id always names a row of features.

Example

The rows must come back in the order shown.

features

feature_idfeature_namelaunched_on
1Kanban Board2024-01-15
2Time Tracking2024-03-01
3AI Summaries2024-06-20
4Audit Log2024-07-01

feature_usage

usage_idfeature_iduser_idused_at
11212024-07-02 10:00:00
21222024-07-02 11:30:00
31212024-07-03 09:00:00
42232024-07-03 12:15:00
53212024-07-04 16:40:00
63242024-07-05 08:20:00
72232024-07-05 18:00:00

Output

feature_nameadopters
AI Summaries2
Kanban Board2
Time Tracking1
Audit Log0
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
feature_idint
feature_namevarchar
launched_ondate

Primary key: feature_id.

One row per feature that has shipped.

Table: feature_usage

ColumnType
usage_idint
feature_idint
user_idint
used_atdatetime

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_idfeature_namelaunched_on
1Kanban Board2024-01-15
2Time Tracking2024-03-01
3AI Summaries2024-06-20
4Audit Log2024-07-01

feature_usage

usage_idfeature_iduser_idused_at
11212024-07-02 10:00:00
21222024-07-02 11:30:00
31212024-07-03 09:00:00
42232024-07-03 12:15:00
53212024-07-04 16:40:00
63242024-07-05 08:20:00
72232024-07-05 18:00:00

Output

feature_nameadopters
AI Summaries2
Kanban Board2
Time Tracking1
Audit Log0

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_name

Another 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_name

Another 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 →