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…