DB Exercises
0
← Exercises/
Business Analytics•5 of 14
PrevNext
Business AnalyticsAdvancedPRO

RFM (Recency, Frequency, Monetary) Customer Segmentation

Scenario

E-commerce CRM marketing divides customers by RFM metrics: Recency (days between a reference audit date of '2024-04-01' and customer's latest order), Frequency (order count), and Monetary (total spend).

Task
Group records in `ecom_transactions` by `customer_id`. Calculate: 1. `customer_id` 2. `recency_days`: `DATE '2024-04-01' - MAX(order_date)` 3. `order_frequency`: `COUNT(*)` 4. `monetary_total`: `SUM(amount)` Order results by `monetary_total` descending, then by `customer_id` ascending.
Expected Output Format

Customer profiles with Recency, Frequency, and Monetary metrics.

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 “RFM (Recency, Frequency, Monetary) Customer Segmentation” SQL Problem

Business Scenario

E-commerce CRM marketing divides customers by RFM metrics: Recency (days between a reference audit date of '2024-04-01' and customer's latest order), Frequency (order count), and Monetary (total spend).

Expected Output & Criteria

Customer profiles with Recency, Frequency, and Monetary metrics.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE ecom_transactions (
        txn_id INT PRIMARY KEY,
        customer_id INT NOT NULL,
        order_date DATE NOT NULL,
        amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYSUMCOUNTORDER BY

Want more Business Analytics practice?

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

View Business Analytics Track→