DB Exercises
0
← Exercises/
Joins•10 of 12
PrevNext
JoinsAdvancedPRO

General Ledger Bank Reconciliation with FULL OUTER JOIN

Scenario

Accounting must reconcile internal ledger records against external bank statements. Any transaction appearing on only one side must be flagged as either 'Missing in Bank' or 'Missing in Ledger'.

Task
Join `internal_ledger` with `bank_statement_lines` using a `FULL OUTER JOIN` on `transaction_ref`. Calculate: 1. `COALESCE(l.transaction_ref, b.transaction_ref)` AS `ref_code` 2. `l.ledger_amount` 3. `b.statement_amount` 4. `reconciliation_status`: - When `l.transaction_ref IS NULL` THEN `'Missing in Ledger'` - When `b.transaction_ref IS NULL` THEN `'Missing in Bank'` - ELSE `'Matched'` Order results by `ref_code` ascending.
Expected Output Format

Reconciliation table classifying each transaction as Matched, Missing in Bank, or Missing in Ledger.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “General Ledger Bank Reconciliation with FULL OUTER JOIN” SQL Problem

Business Scenario

Accounting must reconcile internal ledger records against external bank statements. Any transaction appearing on only one side must be flagged as either 'Missing in Bank' or 'Missing in Ledger'.

Expected Output & Criteria

Reconciliation table classifying each transaction as Matched, Missing in Bank, or Missing in Ledger.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE internal_ledger (
        entry_id INT PRIMARY KEY,
        transaction_ref VARCHAR(30) UNIQUE NOT NULL,
        ledger_amount NUMERIC(10, 2) NOT NULL
      );

      CREATE TABLE bank_statement_lines (
        line_id INT PRIMARY KEY,
        transaction_ref VARCHAR(30) UNIQUE NOT NULL,
        statement_amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

FULL OUTER JOINCOALESCECASE

Want more Joins practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Joins Track→