DB Exercises
0
← Exercises/
CTEs•2 of 12
PrevNext
CTEsAdvanced

Net New MRR Bridge: New, Expansion, and Contraction

Scenario

SaaS financial reporting decomposes recurring revenue into three distinct movements: new acquisition MRR, expansion MRR from upgrades, and contraction MRR from downgrades.

Task
Write a modular query using a CTE to aggregate each movement type from `mrr_movements`: 1. `new_mrr`: Sum of `movement_amount` where `movement_type = 'new'` 2. `expansion_mrr`: Sum of `movement_amount` where `movement_type = 'expansion'` 3. `contraction_mrr`: Sum of `movement_amount` where `movement_type = 'contraction'` 4. `net_new_mrr`: `new_mrr + expansion_mrr - contraction_mrr` Return these four metrics in a single summary row.
Expected Output Format

Single row summary showing new_mrr, expansion_mrr, contraction_mrr, and net_new_mrr.

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 “Net New MRR Bridge: New, Expansion, and Contraction” SQL Problem

Business Scenario

SaaS financial reporting decomposes recurring revenue into three distinct movements: new acquisition MRR, expansion MRR from upgrades, and contraction MRR from downgrades.

Expected Output & Criteria

Single row summary showing new_mrr, expansion_mrr, contraction_mrr, and net_new_mrr.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE mrr_movements (
        movement_id INT PRIMARY KEY,
        account_id INT NOT NULL,
        movement_type VARCHAR(20) NOT NULL,
        movement_amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHSUM

Want more CTEs practice?

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

View CTEs Track→