dbdbexercises
Practice Free
← Syllabus|
Hard

16. RFM Customer Segmentation Score & Quartile Assignment

HardBusiness AnalyticsPostgreSQL 16PRO
Table: customer_rfm_summary
+--------------+------------+
| Column Name  | Type       |
+--------------+------------+
| customer_id  | int        |
| total_orders | int        |
| total_spend  | numeric(10 |
+--------------+------------+

customer_id is the unique key for this table. Each row contains record details for this enterprise dataset.

CRM marketing automation divides customer bases into behavioral quartiles across Monetary spend to tailor campaign promotions.

For each customer in `customer_rfm_summary`, assign a `monetary_quartile` from 1 to 4 using `NTILE(4) OVER (ORDER BY total_spend DESC)`. Quartile 1 contains the top 25% highest spenders. Return: 1. `customer_id` 2. `total_orders` 3. `total_spend` 4. `monetary_quartile` Order results by `monetary_quartile` ascending, then by `total_spend` descending, then by `customer_id` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Customer monetary quartile distribution from top to bottom tier.

Constraints:
  • Query must utilize clause(s): NTILEOVERORDER BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “RFM Customer Segmentation Score & Quartile Assignment” SQL Problem

Business Scenario

CRM marketing automation divides customer bases into behavioral quartiles across Monetary spend to tailor campaign promotions.

Expected Output & Criteria

Customer monetary quartile distribution from top to bottom tier.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE customer_rfm_summary (
        customer_id INT PRIMARY KEY,
        total_orders INT NOT NULL,
        total_spend NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

NTILEOVERORDER BY

Want more Business Analytics practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Business Analytics Track