DB Exercises
0
← Exercises/
Aggregations•12 of 12
PrevNext
AggregationsIntermediatePRO

Churned MRR Concentration by Industry Sector

Scenario

The board of directors wants an audit of lost monthly recurring revenue (MRR) due to customer cancellations. They need to identify industry verticals that experienced at least $3,000 in lost MRR.

Task
Group records in `saas_contracts` by `industry_sector`. Calculate: 1. `industry_sector` 2. `churned_logos`: Count of accounts where `contract_status = 'churned'` 3. `lost_mrr`: Sum of `mrr_value` for churned accounts 4. `avg_lost_mrr`: Average `mrr_value` of churned accounts, rounded to 2 decimal places Filter records with `WHERE contract_status = 'churned'`. Use `HAVING` to only include industries where `SUM(mrr_value) >= 3000.00`. Order results by `lost_mrr` descending.
Expected Output Format

Summary of lost MRR and churned accounts per industry sector with lost MRR >= $3,000.

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 “Churned MRR Concentration by Industry Sector” SQL Problem

Business Scenario

The board of directors wants an audit of lost monthly recurring revenue (MRR) due to customer cancellations. They need to identify industry verticals that experienced at least $3,000 in lost MRR.

Expected Output & Criteria

Summary of lost MRR and churned accounts per industry sector with lost MRR >= $3,000.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE saas_contracts (
        contract_id INT PRIMARY KEY,
        company_name VARCHAR(100) NOT NULL,
        industry_sector VARCHAR(50) NOT NULL,
        mrr_value NUMERIC(10, 2) NOT NULL,
        contract_status VARCHAR(20) 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→