SQL Intermediate Lesson 10: Project – Customer RFM and Outstanding Payments

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 10 of 10

Two reports every sales and accounts team asks for, built only with what this course covered.

1. RFM: who are your best customers?

• Recency: days since the last order (lower is better).
• Frequency: number of orders.
• Monetary: total value.

WITH r AS (
  SELECT customer,
         julianday('2026-10-01') - julianday(MAX(order_date)) AS rec,
         COUNT(*) AS f, SUM(amount) AS m
  FROM sales GROUP BY customer
), s AS (
  SELECT customer,
         NTILE(3) OVER (ORDER BY rec DESC) AS rs,   -- recent = 3
         NTILE(3) OVER (ORDER BY f)        AS fs,
         NTILE(3) OVER (ORDER BY m)        AS ms
  FROM r
)
SELECT customer, rs, fs, ms,
       CASE WHEN rs = 3 AND fs = 3 AND ms = 3 THEN 'Champion'
            WHEN rs = 1 THEN 'At risk' ELSE 'Regular' END AS segment
FROM s;

“At risk” with high frequency and money, like a big customer who has gone quiet, is the call list for Monday morning.

2. Outstanding receivables

WITH inv  AS (SELECT o.order_id, o.customer_id, o.qty * p.price * (1 + p.gst_rate) AS due
              FROM orders o JOIN products p USING (product_id)
              WHERE o.status IN ('Delivered', 'Shipped')),
     paid AS (SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id)
SELECT c.name, ROUND(SUM(inv.due - COALESCE(paid.paid, 0)), 2) AS outstanding
FROM inv LEFT JOIN paid USING (order_id) JOIN customers c USING (customer_id)
GROUP BY c.name HAVING outstanding > 1
ORDER BY outstanding DESC;

Note the paid CTE aggregates payments before the join, so orders paid in instalments aren’t double-counted (lesson 5).

Next: build the same analysis in Python with pandas, or visualise it in the Power BI course.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.