Top Two Features in Every Pricing Plan — SQL Problem
Packaging decisions start from what each tier's customers actually use.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #410
Problem statement
Packaging decisions start from what each tier's customers actually use. A feature's popularity in a plan is the number of distinct users on that plan who used it. A feature is in a plan's top two when fewer than two features of that plan have strictly more users — so ties share a place and can push the list past two (counts 5, 5, 3 give two features; 5, 3, 3 give three).
Return plan, feature and users, ordered by plan, then users descending, then feature. Plans with no usage do not appear.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| plan | enum(starter, growth, enterprise) |
Primary key: user_id.
One row per user with the plan their workspace pays for.
Table: feature_events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| feature | varchar |
| used_at | datetime |
Primary key: event_id.
One row per use of a feature; user_id always names a row of users.
Examples
Example 1
users
| user_id | plan |
|---|---|
| 1 | starter |
| 2 | starter |
| 3 | starter |
| 4 | growth |
| 5 | growth |
| 6 | enterprise |
feature_events
| event_id | user_id | feature | used_at |
|---|---|---|---|
| 1 | 1 | Kanban Board | 2024-07-01 10:00:00 |
| 2 | 2 | Kanban Board | 2024-07-01 11:00:00 |
| 3 | 3 | Kanban Board | 2024-07-02 09:00:00 |
| 4 | 1 | Time Tracking | 2024-07-02 10:30:00 |
| 5 | 2 | Time Tracking | 2024-07-03 15:00:00 |
| 6 | 3 | Invoicing | 2024-07-03 16:00:00 |
| 7 | 4 | Gantt Chart | 2024-07-01 12:00:00 |
| 8 | 5 | Gantt Chart | 2024-07-04 12:00:00 |
| 9 | 4 | AI Summaries | 2024-07-02 14:00:00 |
| 10 | 5 | AI Summaries | 2024-07-05 09:15:00 |
| 11 | 4 | Audit Log | 2024-07-06 18:00:00 |
| 12 | 6 | Audit Log | 2024-07-06 19:00:00 |
Output
| plan | feature | users |
|---|---|---|
| enterprise | Audit Log | 1 |
| growth | AI Summaries | 2 |
| growth | Gantt Chart | 2 |
| starter | Kanban Board | 3 |
| starter | Time Tracking | 2 |
How to solve Top Two Features in Every Pricing Plan
Join each event to its user's plan and count distinct users per (plan, feature) — a user who opens the Kanban board ten times is one user. That gives a small table to rank.
The tie rule — a feature qualifies when fewer than two features in its plan have strictly more users — is the definition of RANK(): a row's rank is one plus the number of rows strictly ahead of it. So RANK() OVER (PARTITION BY plan ORDER BY users DESC) <= 2. With counts 2, 2, 1 both twos rank 1 and the one ranks 3, so two features return; with 3, 2, 2 the twos share rank 2 and three return. DENSE_RANK would wrongly admit the 1 in the first case (dense rank 2), and ROW_NUMBER would arbitrarily drop one of a tied pair.
The correlated alternative writes the rule literally — count the plan's features with more users and keep those with fewer than two. It is quadratic in features per plan, which is tiny here; the window version is a sort per plan.
Reference solution (MySQL)
WITH per_feature AS (
SELECT u.plan, e.feature, COUNT(DISTINCT e.user_id) AS users
FROM feature_events e
JOIN users u ON u.user_id = e.user_id
GROUP BY u.plan, e.feature
), ranked AS (
SELECT plan, feature, users, RANK() OVER (PARTITION BY plan ORDER BY users DESC) AS rk
FROM per_feature
)
SELECT plan, feature, users
FROM ranked
WHERE rk <= 2
ORDER BY plan, users DESC, featureAnother way
WITH per_feature AS (
SELECT u.plan, e.feature, COUNT(DISTINCT e.user_id) AS users
FROM feature_events e JOIN users u ON u.user_id = e.user_id GROUP BY u.plan, e.feature
)
SELECT p.plan, p.feature, p.users FROM per_feature p
WHERE (SELECT COUNT(*) FROM per_feature q WHERE q.plan = p.plan AND q.users > p.users) < 2
ORDER BY p.plan, p.users DESC, p.feature← Paid Accounts Dormant for Over 30 Days · Power Users Active on Four or More Days a Month →