Active Subscriptions Above the Average MRR — SQL Problem
Account managers are assigned to the larger customers. A subscription is large when its monthly recurring revenue (mrr, in rupees) is strictly above the…
- Difficulty: Easy
- Topics: Subqueries, Basics
- Dialect: MySQL
- Problem: #403
Problem statement
Account managers are assigned to the larger customers. A subscription is large when its monthly recurring revenue (mrr, in rupees) is strictly above the average MRR of active subscriptions; cancelled and paused subscriptions neither count towards the average nor appear in the answer.
Return subscription_id, account_name and mrr, ordered by mrr descending, then subscription_id ascending.
Tables
Table: subscriptions
| Column | Type |
|---|---|
| subscription_id | int |
| account_name | varchar |
| plan | enum(starter, growth, enterprise) |
| mrr | int |
| status | enum(active, paused, cancelled) |
Primary key: subscription_id.
One row per subscription; mrr is the monthly amount billed in rupees.
Examples
Example 1
subscriptions
| subscription_id | account_name | plan | mrr | status |
|---|---|---|---|---|
| 101 | Zentrix Labs | enterprise | 85000 | active |
| 102 | Quillpad | starter | 4999 | active |
| 103 | Ledgerly | growth | 24999 | active |
| 104 | Orbitpay | enterprise | 120000 | cancelled |
| 105 | Vaani AI | growth | 39999 | active |
| 106 | Tiffinly | growth | 39999 | active |
| 107 | Mintmark | starter | 4999 | paused |
Output
| subscription_id | account_name | mrr |
|---|---|---|
| 101 | Zentrix Labs | 85000 |
| 105 | Vaani AI | 39999 |
| 106 | Tiffinly | 39999 |
How to solve Active Subscriptions Above the Average MRR
The comparison value is a single number — the mean MRR of active subscriptions — so it is a scalar subquery: (SELECT AVG(mrr) FROM subscriptions WHERE status = 'active'). The outer query keeps the active subscriptions strictly above it and sorts.
The status filter is needed in both places, and they mean different things. Inside the subquery it decides which rows form the average (a cancelled ₹1,20,000 contract would otherwise pull it up); outside it decides which rows may be returned. Forgetting either gives a different answer.
The subquery does not depend on the outer row, so it is computed once. Joining a one-row derived table is the same plan written as a join. The third version avoids the average's decimals altogether by comparing mrr * count > total, which is exact in integers — a useful trick when ratios worry you. If no subscription is active the average is NULL, the comparison is never true, and the answer is empty.
Reference solution (MySQL)
SELECT subscription_id, account_name, mrr
FROM subscriptions
WHERE status = 'active'
AND mrr > (SELECT AVG(mrr) FROM subscriptions WHERE status = 'active')
ORDER BY mrr DESC, subscription_idAnother way
SELECT s.subscription_id, s.account_name, s.mrr FROM subscriptions s CROSS JOIN (SELECT AVG(mrr) AS avg_mrr FROM subscriptions WHERE status = 'active') a WHERE s.status = 'active' AND s.mrr > a.avg_mrr ORDER BY s.mrr DESC, s.subscription_idAnother way
WITH a AS (SELECT SUM(mrr) AS total, COUNT(*) AS n FROM subscriptions WHERE status = 'active') SELECT s.subscription_id, s.account_name, s.mrr FROM subscriptions s, a WHERE s.status = 'active' AND s.mrr * a.n > a.total ORDER BY s.mrr DESC, s.subscription_id← Workspace Seat Utilisation Status · Free Trials Ending Within a Week →