Aggregate KPIs with explicit denominators — Smart Study Module 8

A control report needs both the total and evidence that exceptions were not omitted.

Outcomes

• Separate row filters from group filters
• Handle NULL deliberately
• Define DPD bucket boundaries

Understand

WHERE filters rows before aggregation; HAVING filters grouped results. COUNT(*) counts rows, while COUNT(amount) excludes NULL values. If you only inspect SUM(amount), a missing amount can disappear unnoticed.

CASE implements explicit business rules. Use mutually exclusive DPD boundaries and a deliberate Unknown result for missing DPD, rather than treating it as current.

A percentage requires a stated population. January collection efficiency here is payment amount divided by branch-month target, not payment divided by POS. POS measures outstanding principal at a snapshot date.

Follow along

1. Sum January payments with the supplied SQL.
2. Compare COUNT(*) with COUNT(amount).
3. Build DPD bucket counts from loans.
4. Try a temporary NULL amount in a copy and inspect both controls.

Worked example

SELECT SUM(amount) AS collection, COUNT(*) AS events, COUNT(amount) AS valued
FROM payments WHERE payment_date >= '2026-01-01' AND payment_date < '2026-02-01';

collection=48000, events=5, valued=5. Two loans are 90+ DPD.

Practice

How many loan accounts have dpd >= 90?

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.