SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.
src_balances is the source of truth and tgt_balances is the copy loaded elsewhere. Both are keyed by account_id. Return every account that does not reconcile, with a status: missing_in_target: in the source but not in the target extra_in_target: in the target but not in the source amount_mismatch: in both but amount differs. NULL and NULL are equal; NULL and a number are different. Accounts that match are not returned. Columns: account_id, status, src_amount, tgt_amount. Order by account_id.
Input: src_balances account_id | amount 1 | 100 2 | 200 3 | 300 4 | NULL 5 | 500 tgt_balances account_id | amount 1 | 100 2 | 250 4 | NULL 5 | NULL 6 | 600 Output: account_id | status | src_amount | tgt_amount 2 | amount_mismatch | 200 | 250 3 | missing_in_target | 300 | NULL 5 | amount_mismatch | 500 | NULL 6 | extra_in_target | NULL | 600 Why this passes: Account 2 differs (200 vs 250). Account 3 is missing in the target. Account 5 differs because the target amount is NULL. Account 6 exists only in the target. Accounts 1 and 4 reconcile.
Input: src_balances account_id | amount 1 | 10 2 | 20 tgt_balances account_id | amount 1 | 11 Output: account_id | status | src_amount | tgt_amount 1 | amount_mismatch | 10 | 11 2 | missing_in_target | 20 | NULL Why this passes: One mismatch and one missing account.
Topics: lakebench, sql, reconciliation, full outer join, null-safe.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: List accounts that are missing in the target, extra in the target, or present in both with different amounts.
`src_balances` is the source of truth and `tgt_balances` is the copy loaded elsewhere. Both are keyed by `account_id`. Return every account that does not reconcile, with a `status`: - `missing_in_target`: in the source but not in the target - `extra_in_target`: in the target but not in the source - `amount_mismatch`: in both but `amount` differs. NULL and NULL are equal; NULL and a number are different. Accounts that match are not returned. Columns: `account_id`, `status`, `src_amount`, `tgt_amount`. Order by `account_id`.