HardCTEs High Frequency

Net New MRR Bridge: New, Expansion, and Contraction

Frequently Asked By:MicrosoftGoogleStripe

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

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.

Why Technical Interviewers Ask This

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

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass ctes constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH aggregates AS (
        SELECT 
          SUM(CASE WHEN movement_type = 'new' THEN movement_amount ELSE 0 END) AS new_mrr,
          SUM(CASE WHEN movement_type = 'expansion' THEN movement_amount ELSE 0 END) AS expansion_mrr,
          SUM(CASE WHEN movement_type = 'contraction' THEN movement_amount ELSE 0 END) AS contraction_mrr
        FROM mrr_movements
      )
      SELECT 
        new_mrr,
        expansion_mrr,
        contraction_mrr,
        (new_mrr + expansion_mrr - contraction_mrr) AS net_new_mrr
      FROM aggregates;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in CTEs. It meets the target criteria: Single row summary showing new_mrr, expansion_mrr, contraction_mrr, and net_new_mrr.

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Microsoft and Google interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable CTEs queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.