Your question is Cash and Position Reconciliation SQL. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
account_id, as_of_date, both cash values, cash difference, both position values, position difference, and mismatch_type.account_id, then as_of_date.| 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 |
| 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 |