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

Quarterly Logo Churn Rate with CTEs

Scenario

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

Task
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)`
Expected Output Format

Single row summary with total_accounts, churned_accounts, and churn_rate_pct.

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 “Quarterly Logo Churn Rate with CTEs” SQL Problem

Business Scenario

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

Expected Output & Criteria

Single row summary with total_accounts, churned_accounts, and churn_rate_pct.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE saas_customers (
        account_id INT PRIMARY KEY,
        customer_name VARCHAR(100) NOT NULL,
        signup_date DATE NOT NULL,
        status VARCHAR(20) NOT NULL
      );

Target SQL Clauses & Operators

WITHCOUNTROUND

Want more CTEs practice?

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

View CTEs Track→