HardCTEs High Frequency

Quarterly Logo Churn Rate with CTEs

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 Common Table Expression (`WITH ...`) to compute the overall customer churn rate across all accounts. Calculate: 1. `total_accounts`: Total number of customer accounts created 2. `churned_accounts`: Number of accounts with status `'churned'` 3. `churn_rate_pct`: Percentage of accounts churned, rounded to 2 decimal places: `ROUND(100.0 * churned_accounts / total_accounts, 2)`

Why Technical Interviewers Ask This

SaaS venture capital investors judge retention using Quarterly Logo Churn Rate: (churned customers during quarter) / (total customers active at quarter start) * 100%.

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 stats AS (
        SELECT 
          COUNT(*) AS total_accounts,
          COUNT(*) FILTER (WHERE status = 'churned') AS churned_accounts
        FROM saas_customers
      )
      SELECT 
        total_accounts,
        churned_accounts,
        ROUND(100.0 * churned_accounts / total_accounts, 2) AS churn_rate_pct
      FROM stats;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in CTEs. It meets the target criteria: Single row summary with total_accounts, churned_accounts, and churn_rate_pct.

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.