Deviopath › PostgreSQL

Customer RFM Report

Hard · CTEs

RFM sums up customers by Recency (how long since the last order), Frequency (how many orders) and Monetary value (how much they spent). This report combines a CTE, conditional aggregation (COUNT(*) FILTER (WHERE ...)), a window function and a CASE.

Only orders with status = 'paid' count. The report date is 2026-07-01. For every customer (including customers with no paid orders), return:

- customer: the name

- orders: number of paid orders

- total_spent: sum of their paid amounts, rounded to 2 decimals (0.00 if none)

- recency_days: DATE '2026-07-01' minus the date of their last paid order, as an integer (NULL if none)

- q2_orders: paid orders placed from 2026-04-01 to 2026-06-30 inclusive

- spend_rank: DENSE_RANK() by total_spent descending, over all customers

- segment: the first rule that matches: 'inactive' if no paid orders; 'champion' if recency_days <= 30 and orders >= 3; 'at risk' if recency_days > 90; otherwise 'regular'

Sort by spend_rank, then customer.

Next: Order Total With Tax