Cash and Position Reconciliation SQL | Dataford Interview Questions

Write a SQL query to reconcile cash and position data between a fund manager and a custodian, highlighting mismatches by account and date.

Use the two supplied source tables and include records missing from either source. Treat NULL values as distinct from populated values when determining whether a field mismatches.

Output

One row per account and date with a mismatch.
Return account_id, as_of_date, both cash values, cash difference, both position values, position difference, and mismatch_type.
Order by account_id, then as_of_date.

Schema

fund_manager_balances

Column	Type	Description
account_idPK	VARCHAR(20)	Fund account identifier
as_of_datePK	DATE	Balance valuation date
cash_balance	DECIMAL(18,2)	Cash balance reported by the fund manager
position_value	DECIMAL(18,2)	Total position value reported by the fund manager

custodian_balances

Column	Type	Description
account_idPK	VARCHAR(20)	Custodian account identifier
as_of_datePK	DATE	Balance valuation date
cash_balance	DECIMAL(18,2)	Cash balance reported by the custodian
position_value	DECIMAL(18,2)	Total position value reported by the custodian

Your question is Cash and Position Reconciliation SQL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem