lesson 56 Advanced product analysis · advanced

Revenue & payment reconciliation

Reconcile revenue with payments and identify differences.

the idea

Reconciliation compares independent totals. Sum payments and refunds by invoice before joining. Net collected is payments minus refunds; gap is billed minus net collected. A refund creates a gap under this definition, even when the original bill was paid.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

invoices4 columns · 6 rows

idinteger · key	account_idinteger	periodtext	billed_amountreal
1	1	2025-01	20
2	3	2025-01	80
3	4	2025-01	20
4	6	2025-01	80
5	1	2025-02	20

showing 5 of 6 rows

payments3 columns · 7 rows

idinteger · key	invoice_idinteger	amountreal
1	1	10
2	1	10
3	2	80
4	3	15
5	4	80

showing 5 of 7 rows

refunds3 columns · 4 rows

idinteger · key	invoice_idinteger	amountreal
1	1	2
2	1	3
3	2	10
4	4	5

all 4 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

WITH paid AS(
  SELECT invoice_id,SUM(amount) AS paid_amount
  FROM payments
  GROUP BY invoice_id
),returned AS(
  SELECT invoice_id,SUM(amount) AS refund_amount
  FROM refunds
  GROUP BY invoice_id
)
SELECT
  i.id,
  i.billed_amount,
  COALESCE(p.paid_amount,0)-COALESCE(r.refund_amount,0) AS net_collected,
  i.billed_amount-COALESCE(p.paid_amount,0)+COALESCE(r.refund_amount,0) AS gap
FROM invoices i
LEFT JOIN paid p ON p.invoice_id=i.id
LEFT JOIN returned r ON r.invoice_id=i.id;

Watch for: Unmatched payments need a separate investigation; they are not assigned to an invoice here.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Inspect split payments. Invoice 1 has two payments. Refunds also have multiple rows.
step-1.sql
sql›
```
SELECT *
FROM payments
WHERE invoice_id=1;
```

Aggregate payments. One summary row per invoice.

step-2.sql

SELECT invoice_id,SUM(amount) AS paid_amount
FROM payments
GROUP BY invoice_id;

3
Aggregate refunds. Join this summary, not individual refund rows.
step-3.sql
sql›
```
SELECT invoice_id,SUM(amount) AS refund_amount
FROM refunds
GROUP BY invoice_id;
```

Calculate the reconciliation gap. Unmatched payments need a separate investigation; they are not assigned to an invoice here.

step-4.sql

WITH paid AS(
  SELECT invoice_id,SUM(amount) AS paid_amount
  FROM payments
  GROUP BY invoice_id
),returned AS(
  SELECT invoice_id,SUM(amount) AS refund_amount
  FROM refunds
  GROUP BY invoice_id
)
SELECT
  i.id,
  i.billed_amount,
  COALESCE(p.paid_amount,0)-COALESCE(r.refund_amount,0) AS net_collected,
  i.billed_amount-COALESCE(p.paid_amount,0)+COALESCE(r.refund_amount,0) AS gap
FROM invoices i
LEFT JOIN paid p ON p.invoice_id=i.id
LEFT JOIN returned r ON r.invoice_id=i.id