Join without multiplying balances — Smart Study Module 9

One loan has two January payments. A naive joined POS total is wrong even though every row is valid.

Outcomes

• Distinguish one-to-many from one-to-one
• Preserve unpaid loans with LEFT JOIN
• Aggregate events before adding snapshot measures

Understand

INNER JOIN returns matching pairs. LEFT JOIN retains all left rows and supplies NULLs for unmatched right rows. A right-table filter in WHERE can accidentally turn a LEFT JOIN into an inner join.

L004 has two January payments. Joining raw payment rows to Loans repeats its ₹400,000 POS. Summing POS on that join gives ₹1,400,000 rather than ₹1,000,000.

Aggregate payments per loan first, then join that one-row-per-loan result to Loans. SUM(DISTINCT pos) is not a safe repair: two different loans may have equal balances.

Follow along

1. Execute the unsafe join on a temporary query and observe duplicate L004.
2. Build a January payment CTE grouped by loan_id.
3. LEFT JOIN it to Loans and COALESCE missing collection to zero.
4. Check that four loans remain and POS is ₹1,000,000.

Worked example

WITH jan AS (SELECT loan_id,SUM(amount) AS collected FROM payments
WHERE payment_date >= '2026-01-01' AND payment_date < '2026-02-01' GROUP BY loan_id)
SELECT l.loan_id,l.pos,COALESCE(j.collected,0) AS collected
FROM loans l LEFT JOIN jan j ON j.loan_id=l.loan_id ORDER BY l.loan_id;

L004 January collected=14000; safe loan-grain POS total=1000000.

Practice

Return the correct January collection for L004.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.