Drill 8 of 8 in Fintech questions
0

Interview Question Bank · FULL OUTER JOIN · IS DISTINCT FROM · CASE

8. Reconcile the books with the bank

HardFintechQuestion #108

Concept: FULL OUTER JOIN · IS DISTINCT FROM · CASEshow

Reconciliation compares two lists that should agree. FULL OUTER JOIN on the shared key keeps rows from both sides, with NULLs where one side has no match. A comparison with NULL is never true, so a plain a.amount <> b.amount silently drops the one-sided rows; IS DISTINCT FROM treats a missing side as a difference. A CASE that checks which side is NULL then labels each break.

Example (different data):

SELECT COALESCE(s.sku, w.sku) AS sku, s.qty AS system_qty, w.qty AS counted_qty
FROM stock_system s
FULL OUTER JOIN warehouse_count w ON w.sku = s.sku
WHERE s.qty IS DISTINCT FROM w.qty;

Your task

At month end, Quillpay's finance team matches its own books against the bank statement by payment reference. Different booking and clearing dates are normal; missing lines and different amounts are not.

Match fin_book_entries to fin_bank_lines on ref and return only the breaks: ref, book_amount, bank_amount and status, where status is 'missing_in_bank' (only in the books), 'missing_in_books' (only on the bank statement) or 'amount_mismatch' (on both, with different amounts). Leave out refs whose amounts match, whatever their dates. Sort by ref.

Starting Postgres in your browser…

Checking your access…