DB Exercises
0
← Exercises/
Aggregations•1 of 12
PrevNext
AggregationsIntermediate

MRR Breakdown by Subscription Tier

Scenario

The Chief Revenue Officer needs a summary of active revenue distribution across customer tiers. Tiers with fewer than 2 active accounts should be filtered out to protect customer confidentiality.

Task
Aggregate the `customer_accounts` table for all **active** accounts. Calculate: 1. `tier` 2. `total_mrr`: Sum of `mrr` 3. `active_customers`: Count of accounts in that tier 4. `avg_seats`: Average `seat_count` rounded to 1 decimal place Filter to keep only tiers with **at least 2** active customers using a `HAVING` clause. Order the results by `total_mrr` in **descending** order.
Expected Output Format

Aggregate table showing tier, total_mrr, active_customers, and avg_seats for tiers with >= 2 active accounts.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “MRR Breakdown by Subscription Tier” SQL Problem

Business Scenario

The Chief Revenue Officer needs a summary of active revenue distribution across customer tiers. Tiers with fewer than 2 active accounts should be filtered out to protect customer confidentiality.

Expected Output & Criteria

Aggregate table showing tier, total_mrr, active_customers, and avg_seats for tiers with >= 2 active accounts.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE customer_accounts (
        account_id INT PRIMARY KEY,
        company_name VARCHAR(100) NOT NULL,
        tier VARCHAR(30) NOT NULL,
        status VARCHAR(20) NOT NULL,
        mrr NUMERIC(10, 2) NOT NULL,
        seat_count INT NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYSUMCOUNTHAVING

Want more Aggregations practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Aggregations Track→