Business analysis patterns

01The problem

Harbourline's finance director has a number: ₦1.41 billion of freight charges on delivered shipments hasn't been paid. She needs to know how worried to be. ₦1.4 billion that's a week overdue is normal business. ₦1.4 billion that's six months overdue is a cash-flow crisis and possibly bad debt.

Meanwhile, the sales director wants to know which customers matter most and which are slipping away.

None of these is a new SQL feature. Each is a pattern: a standard analysis that companies everywhere ask for, built from the pieces you already have. Knowing the patterns means you can say "yes, by tomorrow" instead of "let me think about how".

02The concept

Receivables aging

Group unpaid amounts into buckets by how long they've been owed (0–30, 31–60, 61–90, over 90 days) as of a fixed date. The older the bucket, the less likely the money is ever collected. Finance teams review an aging report every month.

Steps: total payments per shipment → outstanding = charge − paid → days since delivery, as of the report date → CASE into buckets → total by bucket.

Pareto (concentration)

How much of revenue comes from the top customers? Sort customers by revenue, take a running total, and divide by the grand total. The row where the running share passes 80% tells you how concentrated the business is. High concentration is a risk: lose one big customer and revenue falls sharply.

RFM segmentation

Score every customer on three things:

• Recency: days since their last order (fewer is better).
• Frequency: how many orders.
• Monetary: how much they've spent.

NTILE(4) OVER (ORDER BY …) splits customers into four equal groups for each measure, scored 1 to 4. A customer scoring 4-4-4 is your best; one with high frequency and money but a recency score of 1 is a valuable customer who's gone quiet, the first call for the account team.

03Example

The aging report as of 31 August 2026:

WITH paid AS (
  SELECT shipment_id, SUM(amount) AS paid
  FROM payments
  GROUP BY shipment_id
),
open_items AS (
  SELECT
    s.shipment_id,
    s.customer_id,
    s.freight_charge - COALESCE(p.paid, 0) AS outstanding,
    julianday('2026-08-31') - julianday(s.delivery_date) AS days_owed
  FROM shipments AS s
  LEFT JOIN paid AS p ON p.shipment_id = s.shipment_id
  WHERE s.status = 'Delivered'
    AND s.freight_charge - COALESCE(p.paid, 0) > 0
)
SELECT
  CASE
    WHEN days_owed <= 30 THEN '0-30 days'
    WHEN days_owed <= 60 THEN '31-60 days'
    WHEN days_owed <= 90 THEN '61-90 days'
    ELSE 'Over 90 days'
  END AS bucket,
  COUNT(*) AS shipments,
  SUM(outstanding) AS outstanding,
  ROUND(100.0 * SUM(outstanding) / SUM(SUM(outstanding)) OVER (), 1) AS pct_of_total
FROM open_items
GROUP BY bucket
ORDER BY MIN(days_owed);

The answer to "how worried?" is very: ₦1.29 billion, 91.6% of everything owed, is more than 90 days overdue, spread over 221 shipments. This isn't slow paperwork; it's old debt. The recommendation writes itself: a collection drive on the over-90 balances, starting with the largest.

04Walkthrough

1. Run the example and check the total: the four buckets add up to ₦1,411,777,000, the same as charged minus received on delivered shipments in lesson 3. Reconcile before you present.
2. Note why paid is a CTE: shipments paid in instalments would otherwise be counted once per payment.
3. Find who owes the old money. Change the final SELECT to group the over-90 items by customer:

WITH paid AS (
  SELECT shipment_id, SUM(amount) AS paid FROM payments GROUP BY shipment_id
)
SELECT c.company_name, COUNT(*) AS shipments, SUM(s.freight_charge - COALESCE(p.paid, 0)) AS over_90
FROM shipments AS s
LEFT JOIN paid AS p ON p.shipment_id = s.shipment_id
JOIN customers AS c ON c.customer_id = s.customer_id
WHERE s.status = 'Delivered'
  AND s.freight_charge > COALESCE(p.paid, 0)
  AND s.delivery_date <= date('2026-08-31', '-90 days')
GROUP BY c.customer_id, c.company_name
ORDER BY over_90 DESC
LIMIT 10;

4. Oakridge Motors Plc tops the list with about ₦92 million across 8 shipments. 63 customers have something over 90 days, so this is a widespread collections problem, not one bad customer.

05Practice

06Challenge

07More practice

08Check your understanding