Active Subscriptions Above the Average MRR

Easy
403.

Active Subscriptions Above the Average MRR

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

ColumnTypeValues
subscription_idint
account_namevarchar
planenumstarter, growth, enterprise
mrrint
statusenumactive, paused, cancelled

Primary key: subscription_id.

One row per subscription; mrr is the monthly amount billed in rupees.

Example

The rows must come back in the order shown.

subscriptions

subscription_idaccount_nameplanmrrstatus
101Zentrix Labsenterprise85000active
102Quillpadstarter4999active
103Ledgerlygrowth24999active
104Orbitpayenterprise120000cancelled
105Vaani AIgrowth39999active
106Tiffinlygrowth39999active
107Mintmarkstarter4999paused

Output

subscription_idaccount_namemrr
101Zentrix Labs85000
105Vaani AI39999
106Tiffinly39999
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.

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

ColumnType
subscription_idint
account_namevarchar
planenum(starter, growth, enterprise)
mrrint
statusenum(active, paused, cancelled)

Primary key: subscription_id.

One row per subscription; mrr is the monthly amount billed in rupees.

Examples

Example 1

subscriptions

subscription_idaccount_nameplanmrrstatus
101Zentrix Labsenterprise85000active
102Quillpadstarter4999active
103Ledgerlygrowth24999active
104Orbitpayenterprise120000cancelled
105Vaani AIgrowth39999active
106Tiffinlygrowth39999active
107Mintmarkstarter4999paused

Output

subscription_idaccount_namemrr
101Zentrix Labs85000
105Vaani AI39999
106Tiffinly39999

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_id

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

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