DB Exercises
0
← Exercises/
CTEs•9 of 12
PrevNext
CTEsAdvancedPRO

Customer Lifetime Value Decile Segmentation

Scenario

E-commerce CRM marketing divides customers into 4 spend tiers (quartiles) to direct high-touch concierge support to the top 25% of spenders.

Task
1. In a CTE `customer_ltv`, sum `total_spent` across all purchases in `ecom_orders` for each `customer_id`. 2. In the outer query, use `NTILE(4)` ordered by `lifetime_spend DESC` to assign an `ltv_quartile` (1 = top spenders, 4 = lowest spenders). Return: 1. `customer_id` 2. `lifetime_spend` 3. `ltv_quartile` Order results by `lifetime_spend` descending, then by `customer_id` ascending.
Expected Output Format

Customer lifetime spend with quartile segmentation.

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 “Customer Lifetime Value Decile Segmentation” SQL Problem

Business Scenario

E-commerce CRM marketing divides customers into 4 spend tiers (quartiles) to direct high-touch concierge support to the top 25% of spenders.

Expected Output & Criteria

Customer lifetime spend with quartile segmentation.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE ecom_orders (
        order_id INT PRIMARY KEY,
        customer_id INT NOT NULL,
        order_amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHNTILEGROUP BY

Want more CTEs practice?

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

View CTEs Track→