Top Two Features in Every Pricing Plan

Medium
410.

Top Two Features in Every Pricing Plan

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

ColumnTypeValues
user_idint
planenumstarter, growth, enterprise

Primary key: user_id.

One row per user with the plan their workspace pays for.

Table: feature_events

ColumnType
event_idint
user_idint
featurevarchar
used_atdatetime

Primary key: event_id.

One row per use of a feature; user_id always names a row of users.

Example

The rows must come back in the order shown.

users

user_idplan
1starter
2starter
3starter
4growth
5growth
6enterprise

feature_events

event_iduser_idfeatureused_at
11Kanban Board2024-07-01 10:00:00
22Kanban Board2024-07-01 11:00:00
33Kanban Board2024-07-02 09:00:00
41Time Tracking2024-07-02 10:30:00
52Time Tracking2024-07-03 15:00:00
63Invoicing2024-07-03 16:00:00
74Gantt Chart2024-07-01 12:00:00
85Gantt Chart2024-07-04 12:00:00
94AI Summaries2024-07-02 14:00:00
105AI Summaries2024-07-05 09:15:00
2 more rows

Output

planfeatureusers
enterpriseAudit Log1
growthAI Summaries2
growthGantt Chart2
starterKanban Board3
starterTime Tracking2
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.

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

ColumnType
user_idint
planenum(starter, growth, enterprise)

Primary key: user_id.

One row per user with the plan their workspace pays for.

Table: feature_events

ColumnType
event_idint
user_idint
featurevarchar
used_atdatetime

Primary key: event_id.

One row per use of a feature; user_id always names a row of users.

Examples

Example 1

users

user_idplan
1starter
2starter
3starter
4growth
5growth
6enterprise

feature_events

event_iduser_idfeatureused_at
11Kanban Board2024-07-01 10:00:00
22Kanban Board2024-07-01 11:00:00
33Kanban Board2024-07-02 09:00:00
41Time Tracking2024-07-02 10:30:00
52Time Tracking2024-07-03 15:00:00
63Invoicing2024-07-03 16:00:00
74Gantt Chart2024-07-01 12:00:00
85Gantt Chart2024-07-04 12:00:00
94AI Summaries2024-07-02 14:00:00
105AI Summaries2024-07-05 09:15:00
114Audit Log2024-07-06 18:00:00
126Audit Log2024-07-06 19:00:00

Output

planfeatureusers
enterpriseAudit Log1
growthAI Summaries2
growthGantt Chart2
starterKanban Board3
starterTime Tracking2

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, feature

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