RFM Analysis and Customer Segmentation in SQL

What RFM Analysis Is

RFM stands for Recency, Frequency, Monetary — a classic, widely-used customer segmentation framework that scores every customer on three dimensions: how recently they purchased, how often they purchase, and how much they spend. It's a staple in marketing analytics and CRM work, and a realistic, business-grounded SQL exercise that goes beyond textbook queries into something an analytics or growth role would actually build.

The Sample Data

-- orders: one row per completed order
| order_id | customer_id | order_date  | total  |
|----------|-------------|-------------|--------|
| 1        | 101         | 2026-06-01  | 50.00  |
| 2        | 101         | 2026-06-15  | 75.00  |
| 3        | 102         | 2026-01-10  | 200.00 |
| 4        | 103         | 2026-06-20  | 30.00  |

Step 1: Compute Raw R, F, M Values Per Customer

WITH customer_metrics AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(total) AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM customer_metrics;

• Recency — days since the customer's most recent order (lower is better — more recently active)
• Frequency — total number of orders (higher is better)
• Monetary — total amount spent (higher is better)

Step 2: Convert Raw Values Into 1-5 Scores Using NTILE

Raw values aren't directly comparable across dimensions (days vs. order count vs. dollars), so RFM analysis converts each into a relative score, typically 1-5, based on where the customer falls within the overall distribution — NTILE() is built exactly for this.

WITH customer_metrics AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(total) AS monetary
  FROM orders
  GROUP BY customer_id
),
scored AS (
  SELECT
    customer_id,
    recency_days, frequency, monetary,
    -- Recency: LOWER days is better, so score is INVERTED (NTILE 5 = most recent)
    6 - NTILE(5) OVER (ORDER BY recency_days) AS r_score,
    NTILE(5) OVER (ORDER BY frequency) AS f_score,
    NTILE(5) OVER (ORDER BY monetary) AS m_score
  FROM customer_metrics
)
SELECT *, (r_score + f_score + m_score) AS rfm_total
FROM scored
ORDER BY rfm_total DESC;

Watch the recency inversion carefully — for frequency and monetary, a higher raw value should map to a higher score (more orders, more spend = better). For recency, a lower raw value (fewer days since last order) should map to a higher score, which is why the query inverts it with 6 - NTILE(5). Getting this backwards is the single most common RFM implementation mistake and silently produces a segmentation that's the opposite of intended.

Step 3: Map Scores to Named Segments

SELECT
  customer_id, r_score, f_score, m_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN r_score >= 4 AND f_score BETWEEN 2 AND 3 THEN 'Recent Customers'
    WHEN r_score <= 2 AND f_score >= 4 AND m_score >= 4 THEN 'At-Risk High Value'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
    ELSE 'Needs Attention'
  END AS segment
FROM scored;

The exact thresholds and segment names are a business decision, not a fixed formula — different companies define "Champions" or "At-Risk" differently depending on their specific sales cycle and customer base. This is a good thing to state explicitly in an interview: the SQL mechanics are standard, but the segment boundaries should reflect actual business judgment, not an arbitrary universal rule.

A Common Variant: Quintiles vs. Fixed Business Thresholds

The NTILE(5) approach above creates relative quintiles — each score bucket always has roughly 20% of customers, regardless of the actual underlying numbers. An alternative is fixed, absolute thresholds defined by the business ("anyone who ordered in the last 30 days is Recency = 5"), which don't rebalance automatically as the customer base grows or shrinks.

-- Fixed-threshold alternative for recency, instead of NTILE
CASE
  WHEN recency_days <= 30 THEN 5
  WHEN recency_days <= 60 THEN 4
  WHEN recency_days <= 90 THEN 3
  WHEN recency_days <= 180 THEN 2
  ELSE 1
END AS r_score

Quintile-based scoring (NTILE) is more common for exploratory analysis and works reasonably out of the box on any customer base; fixed thresholds are more common in production marketing systems where consistent, stable definitions across time matter more than relative ranking.

Segment-Level Summary

SELECT
  segment,
  COUNT(*) AS customer_count,
  ROUND(AVG(monetary), 2) AS avg_lifetime_value,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_customers
FROM (
  -- the scored + segmented query from above
  SELECT customer_id, monetary, segment FROM scored_and_segmented
) t
GROUP BY segment
ORDER BY avg_lifetime_value DESC;

This is usually the actual deliverable — a segment-level summary a marketing or growth team can act on directly (e.g. "target the At-Risk High Value segment with a win-back campaign"), rather than the raw per-customer scores.

Common Mistakes

• Forgetting to invert the recency score — the most common RFM bug, silently scoring your most recently active customers as your worst.
• Using NTILE without considering ties — with many customers sharing an identical frequency or monetary value (e.g. many one-time buyers), NTILE can produce lumpy, uneven bucket sizes; this is usually acceptable but worth being aware of.
• Treating RFM scores as directly comparable to raw currency or day values — an R score of 5 and an F score of 5 aren't the same "unit"; summing or averaging them (as in the rfm_total example) is a simplification, not a precise mathematical operation.
• Applying one-size-fits-all segment thresholds across very different customer types (e.g. a B2B enterprise customer vs. a B2C one-time buyer) without considering whether they should be segmented separately first.

Common Interview Questions

1. Write a query to compute RFM scores for each customer. The three-step pattern above: raw metrics, NTILE scoring, segment mapping.
2. Why does the recency score need to be inverted? Because a lower raw recency value (fewer days since last order) represents better customer engagement, while NTILE by default assigns higher scores to higher raw values.
3. What would you do with the resulting segments as a business? Discuss targeted actions per segment — win-back campaigns for at-risk customers, loyalty rewards for champions, onboarding nudges for new/low-frequency customers.
4. How would you decide between quintile-based (NTILE) and fixed-threshold scoring? NTILE for exploratory, relative analysis; fixed thresholds for stable, comparable definitions over time in a production system.

Frequently Asked Questions

Is RFM analysis only useful for e-commerce?

No — the same Recency/Frequency/Monetary framework applies to any business with repeatable transactions: SaaS usage/billing events, donation platforms, subscription renewals. The underlying orders table just needs to represent whatever "transaction" is meaningful for that business.

Should RFM scores be recalculated regularly?

Yes — customer behavior changes over time, so RFM is typically recomputed on a schedule (weekly or monthly) rather than treated as a one-time, static classification.

Practice RFM and Segmentation

RFM analysis is a great way to demonstrate business-grounded analytical thinking, not just SQL syntax. Try it hands-on with our SQL practice questions, or work through a full customer analytics case study in our case studies. For the window function mechanics behind NTILE, see our complete guide to window functions.